topic Re: Rolling 13month not working with Aggr() and two dimensions in QlikView

Rolling 13month not working with Aggr() and two dimensions

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

Hi All,

I have two dimensions.

DATE and Category

Expression:

aggr(
count({$<DATE={">=$(=Date(vRollPosDate))<=$(=Date(vMaxPosDate))"} >}DISTINCT(Segment))
-
Above(count({$<DATE={">=$(=Date(vRollPosDate))<=$(=Date(vMaxPosDate))"} >}DISTINCT(Segment))
)
,Category,Sort_date)

I have used 'sortdate' as another field to sort date in asc for line chart plotting .

when I implement rolling 13 months logic , upon any date selection I am able to see a single date on the line chart.

but unable to plot 13 months(with any date selection) in the above expression.

* with DATE Dimension only and no use of Aggr in the above exp , I am able to plot rolling 13 months with any date selection.

Would appreciate your inputs.

Thanks a lot

Shreya

Re: Rolling 13month not working with Aggr() and two dimensions

Gysbert_Wassenaar — Mon, 24 Dec 2018 10:22:27 GMT

You might want to try another method called the As-Of Table. See this document for more information: https://community.qlik.com/t5/QlikView-Documents/Calculating-rolling-n-period-totals-averages-or-other/ta-p/1483033

Re: Rolling 13month not working with Aggr() and two dimensions

sunny_talwar — Mon, 24 Dec 2018 12:12:32 GMT

Although I agree with Gysbert that you should def. look into AsOfTable, but give this a shot as well

Only({$<DATE = {">=$(=Date(vRollPosDate))<=$(=Date(vMaxPosDate))"}>} 
Aggr(
Count({$<DATE>} DISTINCT Segment)
-
Above(Count({$<DATE>} DISTINCT Segment))
, Category, Sort_date))

Re: Rolling 13month not working with Aggr() and two dimensions

shreya_nadkarni — Thu, 27 Dec 2018 02:20:17 GMT

Thanks a lot Sunny for your quick response.This expression is working perfectly.(Y)