weekly sum is different from individual sum

[shirleyc40]

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.  

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 ID	Date	Sales
40527	7/17/20	434.97
36056	7/16/20	585
46954	7/16/20	2015.97
46954	7/17/20	2015.97
47496	7/17/20	2173.5
47516	7/17/20	833.5

[Vegar]

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)).

[shirleyc40]

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.

[Vegar · Accepted solution]

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.