topic Re: Set Analysis in Qlikview For Rolling Quarters in QlikView

Set Analysis in Qlikview For Rolling Quarters

dunnalahk123 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis in Qlikview For Rolling Quarters

Anil_Babu_Samineni — Mon, 21 Aug 2017 08:03:56 GMT

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$'),

Re: Set Analysis in Qlikview For Rolling Quarters

dunnalahk123 — Mon, 21 Aug 2017 08:23:10 GMT

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,

Re: Set Analysis in Qlikview For Rolling Quarters

Anil_Babu_Samineni — Mon, 21 Aug 2017 08:31:48 GMT

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)

Re: Set Analysis in Qlikview For Rolling Quarters

dunnalahk123 — Mon, 21 Aug 2017 08:37:38 GMT

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

Re: Set Analysis in Qlikview For Rolling Quarters

Anil_Babu_Samineni — Mon, 21 Aug 2017 08:45:28 GMT

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