Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

weekly sum is different from individual sum

Hi, I'm trying to sum up sales by distinct ID, and I used this function: 

 sum({<CustomerStatusDescription = {'Paid'}>}aggr( sum(Distinct Sales), CustomerID)). The dimension is a drill down of the date. And so for Week 29, the dates I have are 7/12, - 7/18, and the individual day sums are correct, but when I look at the week sum, its a lot lower.  

 

shirleyc40_0-1595361588374.png

The yellow line is the paid status, but all of them act like this.

Hovering it says the for 7/16 the total for paid status is 585. But once I expand into 7/16, the total turns into 2,600.97 (which is correct)

Customer IDDateSales
405277/17/20434.97
360567/16/20585
469547/16/202015.97
469547/17/202015.97
474967/17/20 2173.5
475167/17/20 833.5

 

 

Labels (1)
1 Solution

Accepted Solutions
Highlighted
Partner
Partner

It could also be that you are note aggregating correctly you need week/date in the aggregatin as well. 

sum({<CustomerStatusDescription = {'Paid'}>}aggr( sum(Distinct Sales), CustomerID, Week))

or 

sum({<CustomerStatusDescription = {'Paid'}>}aggr( sum(Distinct Sales), CustomerID, Date))

depending on which dimension you are viewing in your chart.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

3 Replies
Highlighted
Partner
Partner

My first guess (as I don't know how your data is connected) is that it got to do with the field CustomerStatusDescription. What happens if you make a selection on 'Paid', is the result correct then? 

If so then try to add the CustomerStatusDescription modifier to the inner aggregation as well (you might not need it in the outer sum). Like this :

sum({<CustomerStatusDescription = {'Paid'}>}aggr( sum({<CustomerStatusDescription = {'Paid'}>Distinct Sales), CustomerID)).

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Highlighted
Contributor II
Contributor II

using paid as the status filter works with the current equation. I tried moving the condition inside the aggregation sum, but it still says 585.

Highlighted
Partner
Partner

It could also be that you are note aggregating correctly you need week/date in the aggregatin as well. 

sum({<CustomerStatusDescription = {'Paid'}>}aggr( sum(Distinct Sales), CustomerID, Week))

or 

sum({<CustomerStatusDescription = {'Paid'}>}aggr( sum(Distinct Sales), CustomerID, Date))

depending on which dimension you are viewing in your chart.

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post