Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
Partner
Partner

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

Tags (2)
1 Solution

Accepted Solutions

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

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
MVP & Luminary
MVP & Luminary

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

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

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

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

Partner
Partner

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

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