Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

bwisealiahmad
Valued Contributor

Accumulating works with one line, but figures seem to double when I chose another value

Hi,


I am building a line chart where I am trying to compare the amount / # of payments at x amounts of days from the first payment date on a campaign.

The pivot table looks like this (which is correct) (there is two different stores here, blurred the names but pretend it is Store #1 on left and Store #2 on right in the pivot table):

1.JPG

And when I chose Store #1 it accumulates correctly on the line:

2.JPG

The issue is when I add  Store #2 where I also want the line to accumulate the number's don't add up. It is almost as if they double or add up and because of the measure.

3.JPG

The Dimensions are:

1: DATE([Payment Date]) - DATE([First Payment Date) - Gives me amount of days since first payment day which I want the X axis to be.

2: Stores - Gives me different lines

Measure:

RangeSum(Above(Total Sum([Sales] * 1),0,99)) - Used to give me accumulating lines.

What I am suspecting is when I chose two stores the Total part makes it double or add them both and then do some sort of calculation from that when accumulating.

Thougths?

1 Solution

Accepted Solutions
MVP
MVP

Re: Accumulating works with one line, but figures seem to double when I chose another value

Ali Ahmad wrote:

So lets say I do that and named it "DaysBetween"

= aggr (

RangeSum(Above(Total Sum([Sales] * 1),0,99)), Store, DaysBetween)

I think you should remove the TOTAL qualifier, to reset the accumulation at the column segment boundery:

=Aggr( Rangesum(Above(Sum(Sales),0,RowNo() )), Store, (DaysBetween, (NUMERIC, ASCENDING)) )

The sortable Aggr function is finally here!

6 Replies
MVP
MVP

Re: Accumulating works with one line, but figures seem to double when I chose another value

I think that's because your line chart with two dimensions translate to a straight table chart with the two dimensions ordered by you date difference, Stores, so for each date difference, the stores are listed, while you want to have a table which is ordered by Store , then date difference.

You can change this order e.g. using advanced aggregation, but the aggr() function does not allow calculated dimensions.

Can you calculate the date difference already in the script?

MVP
MVP

Re: Accumulating works with one line, but figures seem to double when I chose another value

And if you can workaround the calculated dimension, maybe another option would be an AsOf table approach:

The As-Of Table

bwisealiahmad
Valued Contributor

Re: Accumulating works with one line, but figures seem to double when I chose another value

So lets say I do that and named it "DaysBetween"

= aggr (

RangeSum(Above(Total Sum([Sales] * 1),0,99)), Store, DaysBetween)

bwisealiahmad
Valued Contributor

Re: Accumulating works with one line, but figures seem to double when I chose another value

I've read about this and didn't really give it a go to be honest, but I am going to try!

MVP
MVP

Re: Accumulating works with one line, but figures seem to double when I chose another value

Ali Ahmad wrote:

So lets say I do that and named it "DaysBetween"

= aggr (

RangeSum(Above(Total Sum([Sales] * 1),0,99)), Store, DaysBetween)

I think you should remove the TOTAL qualifier, to reset the accumulation at the column segment boundery:

=Aggr( Rangesum(Above(Sum(Sales),0,RowNo() )), Store, (DaysBetween, (NUMERIC, ASCENDING)) )

The sortable Aggr function is finally here!

bwisealiahmad
Valued Contributor

Re: Accumulating works with one line, but figures seem to double when I chose another value

Worked when I put DaysBetween in the script and then used Aggr().

Thank you so much for the help. Really great stuff!