topic Re: Cumulative aggregation in Qlikview line chart in QlikView

Cumulative aggregation in Qlikview line chart

nsm1234567 — Tue, 17 Apr 2018 12:47:31 GMT

Hi there,

I'm having some trouble with a cumulative sum that I'm hoping someone can assist with . I feel like I'm close, but not quite there. If you look at the attached .qvw, you'll see my rangesum which works as I expect it to in a straight table:

RangeSum(Above(aggr(count(total <[Decision Date],[Champion Challenge Desc]> {<[Appl Origin Code]={'Pre-Qualified'},[PQ Campaign No Application]={">14"}>}[Appl No])/

sum(total <[Decision Date],[Champion Challenge Desc]>{$}distinct([PQ App Volume (Campaign Only)])),[Decision Date],[Champion Challenge Desc]), 0,

RowNo()))

Once I put this in a line chart though, the values didn't accumulate. I thought then that I could just place this within an aggr as I've done in the second straight table. While this leads to there being accumulation, the order of the results is not correct.

Can anyone see where I've gone wrong here?

Regards

Nathan

Re: Cumulative aggregation in Qlikview line chart

sunny_talwar — Tue, 17 Apr 2018 12:53:19 GMT

Are you using QV12 by any chance? If you are, then you can try this

Aggr(RangeSum(Above(aggr(count(total <[Decision Date],[Champion Challenge Desc]> {<[Appl Origin Code]={'Pre-Qualified'},[PQ Campaign No Application]={">14"}>}[Appl No])/

sum(total <[Decision Date],[Champion Challenge Desc]>{$}distinct([PQ App Volume (Campaign Only)])),[Decision Date],[Champion Challenge Desc]), 0,

RowNo())),[Champion Challenge Desc], ([Decision Date], (NUMERIC)))

Re: Cumulative aggregation in Qlikview line chart

nsm1234567 — Tue, 17 Apr 2018 13:08:11 GMT

Hey there,

Thanks so much. That appears to be working (I'm implementing in Qlikview 12 and Qliksense). Are there options for those that need to do this in versions earlier than Qlikview 12?

Re: Cumulative aggregation in Qlikview line chart

sunny_talwar — Tue, 17 Apr 2018 13:10:19 GMT

The only option is to fix the load order of the date field in the script.... the reason is that the Aggr() use the load order from the script and if you fix the load order of date to be ascending (you can do this using a master calendar or other ways) the accumulation will be fixed.

Re: Cumulative aggregation in Qlikview line chart

nsm1234567 — Tue, 17 Apr 2018 13:20:26 GMT

Thanks a lot for the feedback. Quite interesting behaviour. I noticed when I uploaded my first example app that it was showing correctly because I'd loaded it in the order I wanted and had to scramble it to make it "wrong" again! Thanks again.