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

[shreya_nadkarni]

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

[Gysbert_Wassenaar]

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-oth...

[sunny_talwar · Accepted solution]

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))

[shreya_nadkarni]

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