Modulus calculation to find decreasing Year-Quarter

jjc

I am feeling silly because I can't seem to crack this, but maybe I have been starring at it too long. I am trying to find a math function that will yield a single unit decrement of the currently defined Year/Quarter. This is a software UI thing: I am trying to create a script to decrement, in single steps, the current Year/Quarter shown. The issue is that it needs to wrap around back to the start. Hence I was trying to use a Modulus operator.

Here's what I need, decrementing the current quarter:
Q1 -> Q4 (of the prior year)
Q2 -> Q1
Q3 -> Q2
Q4 -> Q3

I can get three of the four conditions via mod(), but I can't seem to work out one calculation that works for all. Adding another number somewhere is fine; I am really just trying to do this without having to also include an IF() statement to look for the Q1->Q4 condition.

It doesn't have to deal with the "Q" string part; I already am splitting the string up and just handling the numeric portion separately. I then rebuild the string. Same with the year; that is handled in a separate step as well. I just need to calculate the quarter number, and I am trying to save 1 line of code. :)

And if anyone cares, this is being done in FileMaker 12 scripting language. Has most of the modern conveniences, but not quite as flexible as most real full-blown languages. This is what I have currently (yields 3 correct results; slightly modified from actual code to make it more readable):

...
res = Mod ( currQnum - 1 ; 4 );
return res;

Here is my working version for incrementing (modified again; Year-wrap around IS done with an IF statement, but not the Qrtr number):

...
newQ = Mod ( currQNum ; 4 ) + 1 ;
...
return newYr & " Q" & newQ

Thanks
Justin

haruspex

The usual trick is to add the base first to make sure it doesn't go negative: Mod(val+4-1; 4)

jjc

It seems like it wouldn't quite work in this situation; I could still end up with a zero result of the mod(); I want a result from 1 through 4. Val = 1 ; Mod (1 + 4 - 1 ; 4 ) = Mod (4) = 0. And if I add 4 to that, it works for that one, but not the others. I have thought of doing a subtraction ( 4 - Mod(...) ) but that just inverts the order of results, yielding f(3) = 1. But it does work for f(1).

haruspex

OK, you didn't say the qtr numbers are in the range 1-4. I assumed 0-3. That's easy: subtract 1 at the start and add it back at the end. So to step back n quarters:
1+Mod(4*n-1+x-n; 4)

jjc

Yeah, I didn't explicitly mention it but my sample data showed it.

Thanks for this function. I will assume that 'x' in your formula is the starting quarter number. n = 1 all the time, in my case.

So that essentially reduces the formula to:
1+Mod(x+2 ; 4 )

When I work through it in my head it works! Now to test it... :)

Thanks!