Set Analysis in Qlikview For Rolling Quarters

[dunnalahk123]

Hi,

Can Some One Help me how to create a variable which should give always Current Quarter + Last 3 Quarters From Current Quarter in

set analysis.

Num(Sum({$<Metric={'Revenue'},Global={'WW'},Quarter={$(vFYQ_1Final)}>}[NumValue]/1000000),'##.##M$'),

in above expression $(vFYQ_1Final) is giving me Current Quarter but i want to create another variable based on exiting Current Quarter Varibale which should give current Quarter + Last 3 Previous Quarters from Current Quarter

For Exmaple: if my current Quarter is 2017-Q3 it should give sum of 2017-Q3+2017Q2+2017Q1+2016Q4

                       If my Current Quarter is 2017-Q4 it should give sum of 2017-Q4+2017Q3+2017Q2+2017Q1

Best Regards,

Hari

[Anil_Babu_Samineni]

You need this, Perhaps

QuarterStart(Max(DateField), -4)

And then set analysis should be this?

Num(Sum({$<Metric={'Revenue'},Global={'WW'},Quarter={<= $(vlast4Quarters)}>}[NumValue]/1000000),'##.##M$'),

[dunnalahk123]

Hi Anil,

Thank you .

you mean QuarterStart(Max(DateField), -4) is this my variable ?

in above expression DateField means my latest Quarter ?

QuarterStart(Max($(vFYQ_1Final)), -4) ?

i did't get it.

Best Regards,

Hk

[Anil_Babu_Samineni]

Let's assume if you select something from Quarter field, Off course the selection is max. The same we are displaying this? You want to show it as whole variable then. It creates Last 4 quarters only

Sum({<DateField={'>=$(=QuarterStart(Max(DateField), -4))<=$(=QuarterEnd(Max(DateField), 0))}>} Sales)

[dunnalahk123]

Hi Anil,

Yes, i want to create a variable which we will give always Current Quarter + Last 3 Quarters From Current Quarter.

For Example: always my table ox represents the latest Quarter data.

But if some one select a Quarter From Quarter Dimension  like 2017-Q3

the varible should  give me 2017-Q3,2017-Q2,2017-Q1,2016-Q4

if some one select  a Quarter From Quarter Dimension  like 2017-Q4

the varible should  give me 2017-Q4,2017-Q3,2017-Q2,2017-Q1

Best Regards

[Anil_Babu_Samineni]

May be try this?

If(GetSelectCount(Quarter)=0, Num(Sum({$<Metric={'Revenue'},Global={'WW'},Quarter={$(vFYQ_1Final)}>}[NumValue]/1000000),'##.##M$'), Num(Sum({$<Metric={'Revenue'},Global={'WW'},Quarter={">=$(vlast4Quarters) <= $(=Max(Quarter))}>}[NumValue]/1000000),'##.##M$'))

Variable try to declare how you define as good format

LET vlast4Quarters = Max(Quarter)-3