Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shreya_nadkarni
Partner - Creator
Partner - Creator

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

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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

3 Replies
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...


talk is cheap, supply exceeds demand
sunny_talwar

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
Partner - Creator
Partner - Creator
Author

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