topic Re: Rolling 5 Quarters in QlikView

Rolling 5 Quarters

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

Hi,

I need to take always 5 rolling Quarters in my data.

first variable i created is to get latest quarter

=Chr (39) & Maxstring( {<ScenarioLabel={'Actuals'}>} Quarter) & Chr (39)

Which gives me 2018-Q1

then i did as below

=$(vMaxQuarterRolling) - 10 to get 2017-Q1

but i am not getting that value. is it some thing i did some mistake , can some one help me here pls.

Best Regards,

YoussefBelloum — Wed, 21 Feb 2018 09:42:41 GMT

Hi,

do you have a master calendar on your model ?

raajaswin — Wed, 21 Feb 2018 10:16:26 GMT

Hi Hk,

create a column in script with

keepchar(columnname,'0123456789') as YR_QTR_NUM

now you will have numbers with you

then get last five quarter with with max(YR_QTR_NUM,1) ,max(YR_QTR_NUM,2) , max(YR_QTR_NUM,3), max(YR_QTR_NUM,4), max(YR_QTR_NUM,5)

Anil_Babu_Samineni — Wed, 21 Feb 2018 10:53:29 GMT

MaxString is not the solution your case, Even thought this can be done i assume you may need AddYears()?

dunnalahk123 — Wed, 21 Feb 2018 11:12:38 GMT

HI,

I Wrote as below

=MID($(vMaxQuarterRolling) ,1,4)-1 &'-' & MID($(vMaxQuarterRolling) ,6,2)

Best Regards

Anil_Babu_Samineni — Wed, 21 Feb 2018 11:15:46 GMT

do you really think, Does this job done? Because, For me not make sense. Instead i would suggest you to do script level to bifurcate each and level.

Anyway, you got work. That is awesome !!!