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: 
bwisealiahmad
Partner - Specialist
Partner - Specialist

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
swuehl
MVP
MVP

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!

View solution in original post

6 Replies
swuehl
MVP
MVP

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?

swuehl
MVP
MVP

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

The As-Of Table

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

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

= aggr (

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

bwisealiahmad
Partner - Specialist
Partner - Specialist
Author

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

swuehl
MVP
MVP

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

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

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