Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
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)).
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.
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.