Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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)
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
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
I'm pretty sure I didn't specify use of the max function.
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
Replace Today() with MaxDate in variable vPrevYr
Hi Anbu,
Its taking all the Quarters for year 2012,2013,2014,2015. Its not limiting to top 4.
Regards
KC