quarterly Case Help

[RT]

Hi everyone need a bit of help.

i have a summery report here is the case.

Case (

Month ( Date_Invoice ) < 4 ; Year ( Date_Invoice ) & " - Q1";

Month ( Date_Invoice ) < 7 ; Year ( Date_Invoice ) & " - Q2";

Month ( Date_Invoice ) < 9 ; Year ( Date_Invoice ) & " - Q3";

Month ( Date_Invoice ) ≤ 12 ; Year ( Date_Invoice ) & " - Q4"

)

the problem is when i get the report Q4 is way off. i have another summery report that i use which automatically takes 4 days from the invoice date here is the let function for this.

Let ( [

trudate = Date_Invoice - 4;

yr = Year (trudate);

mo = Right ("00" & Month (trudate); 2) ;

sortstring = yr & mo ] ;

GetAsNumber (sortstring) )

the report comes out great.

So i thought i could combine these 2 it does work but i get triple quarter results.

here is that attempt.

Let ( [

trudate = Date_Invoice - 4;

yr = Year (trudate);

mo = Right ("00" & Month (trudate); 2) ;

sortstring = yr & mo ] ;

GetAsNumber (sortstring) )

&

Case (

Month ( Date_Invoice ) < 4 ; Year ( Date_Invoice ) & " - Q1";

Month ( Date_Invoice ) < 7 ; Year ( Date_Invoice ) & " - Q2";

Month ( Date_Invoice ) < 9 ; Year ( Date_Invoice ) & " - Q3";

Month ( Date_Invoice ) ≤ 12 ; Year ( Date_Invoice ) & " - Q4"

)

any help would be great.

[comment]

when i get the report Q4 is way off.

What exactly does "way off" mean?

[RT]

Total invoices quarter 2008 - Q1 = 350

Total invoices quarter 2008 - Q2 = 374

Total invoices quarter 2008 - Q3 = 262

Total invoices quarter 2008 - Q4 = 495

Quarter 1 and 2 are good 3 and 4 are way off.

these are counts of invoices as an example.

the work is done between the 1st and the 15th of each month the invoice are created on the 16th of each month

-----

the work is done between the 16th and the 31st of each month the invoice are created on the 1st of each month

this is where the problem is as the count goes into the next year

Edited August 10, 2010 by Guest

[comment]

Try changing the 9 to 10?

BTW, you could make this a lot shorter:

Year ( Date_Invoice ) & " - Q" & Ceiling ( Month ( Date_Invoice ) / 3 )

[RT]

Thanks comment it works you always amaze me

i will play around with your calc as well later

[RT]

Well i am using your new calc.

Year ( Date_Invoice ) & " - Q" & Ceiling ( Month ( Date_Invoice ) / 3 )

and to get the "real" summary i created a new calculation.

DateAdjust = ( Date_Invoice-4 )

so the new full calc is this.

Year ( DateAdjust ) & " - Q" & Ceiling ( Month ( DateAdjust ) / 3 )

it works great thanks, is this the best way to do this.

[comment]

Why not do it all at once =

Let (

d = Date_Invoice - 4

;

Year ( d ) & " - Q" & Ceiling ( Month ( d ) / 3 )

)

[RT]

very clever just when i am pleased with my efforts you find a better way.

thanks