Show latest four Quaters

[jyothish8807]

Hello All,

I have created a Quarter filed in my script using this;

'T' & ceil(Month(Date#(Month,'MM.YYYY')) / 4)&' '& Year(Date#(Month,'MM.YYYY'))  AS Quarter1

Now i want to show Latest Four Quarter in a pivot chart and when ever i updated dates it should automatically takes the latest four Quarters.

Regards

KC

[ankitaag]

Hi,

Please note that Quarter is of 3 months so you need to divide by 3 instead of 4

=ceil(Month(Date)/3) as Quarter

and whereever displaying this quarter just mention the condition in expression as Year=max(Year)

[Gysbert_Wassenaar]

Add another field in the script:

If(Date#(Month,'MM.YYYY')>= QuarterStart(today(),-3), 1, 0) as  InLast4Quarters

Then change your expressions to use that field to filter the data. For example sum(Value) becomes sum({<InLast4Quarters={1}>}Value)

[jyothish8807]

Hi Ankita,

It was the requirement to keep Quarter as /4, because of that i am not able to use Quarterstart and Quarterend functions also.

Regards

KC

[jyothish8807]

Hi Gysbert,

i tried this but its giving and syntex error:

Load

*,

If(Date#(Month,'MM.YYYY')> addyears(Max(Date#(Month,'MM.YYYY')),-1), 1, 0) as  InLast4Quarters

Resident Temp1;

Drop table tablename;

Regards

KC

[Gysbert_Wassenaar]

I'm pretty sure I didn't specify use of the max function.

[jyothish8807]

Sorry for that Gysbert,

I cant take Today() since i  am working on historic data may be 2 months old. that why i tried using Max function.

Regards

KC

[anbu1984]

Replace Today() with MaxDate in variable vPrevYr

[jyothish8807]

Hi Anbu,

Its taking all the Quarters for year 2012,2013,2014,2015. Its not limiting to top 4.

Regards

KC