topic Rolling 8 Quarters in QlikView

Rolling 8 Quarters

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

Hi Experts,

i have three fields

YearQuarter derived from created date),

YearQuarter_shipped (derived from shipped date),

now i need rolling quarters for this.

i have taken YearQuarter and YearQuarter_shipped as dimensions and my expression is as below to get rolling quarters.

=RangeSum(Before(count({<SHIPPED_DATE={'>=$(=QuarterStart(max(SHIPPED_DATE), -8))<=$(=QuarterEnd(max(SHIPPED_DATE), 0))'},SHIPPED_DATE={'*'}>}DISTINCT ITEM))/

(count(TOTAL <YearQuarter>{$<CREATION_DATE={'*'},CREATION_DATE={'>=$(=QuarterStart(max(CREATION_DATE), -8))<=$(=QuarterEnd(max(CREATION_DATE), 0))'}>}DISTINCT ITEM)))

Thanks,

Hima.

Re: Rolling 8 Quarters

Anil_Babu_Samineni — Fri, 16 Feb 2018 14:48:40 GMT

I am confused, Is there any Question?

Re: Rolling 8 Quarters

shiveshsingh — Sat, 17 Feb 2018 03:22:07 GMT

What's the error you getting?

Re: Rolling 8 Quarters

himabinduch — Sat, 17 Feb 2018 05:12:54 GMT

i am getting Zero values for everything not getting any rolling quarters

Re: Rolling 8 Quarters

Anil_Babu_Samineni — Sat, 17 Feb 2018 06:38:41 GMT

Can you do this for now, Whether values returning or not? This is for last 2 years data. If none of them not working, I would suggest to share sample

=Count({<SHIPPED_DATE={">=$(=AddYears(max(SHIPPED_DATE), -2))<=$(=max(SHIPPED_DATE))"}>}DISTINCT ITEM)/

Count(TOTAL <YearQuarter> {$<CREATION_DATE={">=$(=AddYears(max(SHIPPED_DATE), -2))<=$(=max(SHIPPED_DATE))"}>}DISTINCT ITEM)

=RangeSum(Before(count({<SHIPPED_DATE={">=$(=QuarterStart(max(SHIPPED_DATE), -8))<=$(=QuarterEnd(max(SHIPPED_DATE), 0))"},SHIPPED_DATE={'*'}>}DISTINCT ITEM))/

(count(TOTAL <YearQuarter>{$<CREATION_DATE={'*'},CREATION_DATE={">=$(=QuarterStart(max(CREATION_DATE), -8))<=$(=QuarterEnd(max(CREATION_DATE), 0))"}>}DISTINCT ITEM)))

Re: Rolling 8 Quarters

himabinduch — Sun, 18 Feb 2018 10:41:32 GMT

Hi Anil,

i am not getting proper data. could please find my sample application in below link

so the you will get exactly what i required.

https://community.qlik.com/thread/291517?sr=inbox&ru=171708