Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have an issue with AGGR function in a specific case.
Please refer the following inline table:
Sales:
load * inline [
Date, Customer, Product_Code, Amount, Grade
01/01/2019, A, XA, 10, 5
01/01/2019, A, XA, -10, 6
01/01/2019, A, PA, 12, 5
01/01/2019, A, BC, 8, 5
01/01/2019, B, XA, 8, 5
];
I want to see total amount per customer per date and show for each date and customer its max grade.
But, while calculating the max grade (per date & customer) I want to refer only amount which is not equal to 0 (Zero) for the same product.
So in my case, for customer A at 01/01/2019 i expect to see total amount of 20, and max grade of 5 (as the 6 grade is under product "XA", which should be ignored as its total amount is 0).
I added the following AGGR function:
=max({<Product_Code={"=SUM(Amount)<>0"}>}aggr(NODISTINCT max({<Product_Code={"=SUM(Amount)<>0"}>}Grade),Date,Customer))
This function will give the correct result ONLY if I filter the data on customer A.
If the data is not filtered, the grade which will be shown is 6 (which is not what I plan to see...).
Am I missing something? Any ideas?
Thanks a lot!
the first question is how to make the SUM of DATA (expression) only DATA in front of the MAX (on a dimension) ?