Problem with AGGR - show correct answer only while filtering data

[Tomer_St]

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!
Before filtering data. Incorrect GradeData filtrered by Customer A. Correct Grade

[sunny_talwar]

Create a new field combining Customer and Product_Code like this

Sales:
LOAD *,
	 Customer&Product_Code as Customer_Product_Code_Key;
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
];

and then use this expression

Max({<Customer_Product_Code_Key = {"=Sum(Amount) <> 0"}>} Grade)

[Tomer_St]

Hi

Thanks a lot for your reply!
This solution is indeed gives the correct grade 😀

Though,  I have more dimensions in my real table (like "Promotion_Code", "Department" etc).
Do I need to add a key for all combination of Customer and dimension or is there any simpler way to achiave the required result? 

Sales:
LOAD *,
Customer&Product_Code as Customer_Product_Code_Key,
Customer&Promotion_Code as Customer_Promotion_Code_Key;
LOAD * INLINE [
Date, Customer, Product_Code, Promotion_Code, Amount, Grade
01/01/2019, A, XA, AA, 10, 5
01/01/2019, A, XE, AA, -10, 6
01/01/2019, A, PA, AC, 12, 5
01/01/2019, A, BC, AC, 8, 5
01/01/2019, B, XA, AV, 8, 5
];

Thanks a lot

[sunny_talwar]

It comes down to the fact that what are you checking your condition against? In order to use set analysis, you will need to create it, but if you are okay to use Aggr with if statement, you can avoid creating these additional fields and do it directly on the front end, But Aggr with if will give you inferior performance.

[Tomer_St]

I prefer to use AGGR as I need the calculation of the "grade" to be changed according to users selections (department etc.)
But when I use the AGGR as mentioned in my first post I get wrong result, unless I choose specific customer.

Should I add IF statement inside the AGGR function for each case of dimension?

[sunny_talwar]

Aggr will look like this

Max(Aggr(If(Sum(Amount) <> 0, Grade), Customer, Product_Code))

[Tomer_St]

Hi

Thanks a lot for your reply.
This AGGR function will work in this case, thanks!

But, in my real data there are more dimnsions and I cannot make the AGGR function to work in a similar data like mine.
Can you please have a look in the below inline and AGGR function and advise what should be revised in order to make it work also with several dimensions?

load * inline [
Date, Customer, Product_Code, Promotion_Code, Updating_Divsion, Amount, Grade
01/01/2019, A, XB, AB, SERVICE, 10, 5
01/01/2019, A, XA, AB, SALES, -10, 6
01/01/2019, A, PA, LD, SERVICE, 12, 5
01/01/2019, A, BG, AD, SALES, 8, 6
01/01/2019, A, BC, AA, SALES, -8, 6
02/01/2019, A, BC, AA, SERVICE, 8, 5
01/01/2019, B, BC, AB, SALES, 8, 5
02/01/2019, B, FT, AB, SALES, 8, 5
];


The AGGR function:
=Max(Aggr(If(Sum(Amount) <> 0, Grade), Date, Customer, Product_Code, Promotion_Code, Updating_Divsion))

This function will split the Grade for the same customer and date according to other dimensions (promotion_code, product_code, updated_division) and will not give only one max Grade which should be 5 in Customer A, 01/01/2019.

(All 6 grades should be ignored in this case as their amount per dimension - updated_division in one case and Promotion_Code in the other, are 0).

Thanks again

[sunny_talwar]

I guess the question is... what exactly is your expected output based on the sample below and why do you want that?

[Tomer_St]

Hi

I have data of sales and refunds (amount) with lots of dimension, like division, product, product type, sale type etc.
Each saling division has grade, according to its importancy. This grade is permanent per division.

If a high division grade made the amount change the user is not concerned about lower division.

The request in the bottom line is to point the division with the higest grade which made the sale or the refund per customer per day.
But, a division should be ignored in case of total 0 amount. 

Also, the user should be able to make dimension selection in the dashboard and the result should be changed accordingly.

For example, for customer A in my last inline code:
If division "SALES" (which has grade 6, the highest division grade) made the sale, but the total amout is 0 (-10$ for product XA and +10$ for product XB and also -8$ and +8$ under same division "SALES") - in this case the result should be a lower division (in my inline load its division "SERVICE", with grade 5).

For the same case, if the user make a selection of "excluded product XA", the result should be grade 6 (division "SALES") as its total amount  without product XA is -10$ and "SALES" is the highest division which made a sales (or refund in this case) which is not 0.

I understand its not so simple and sorry if my explanation is not clear enough... 
Though, I hope it will be sufficiant enough in order for you to help me get the correct formula which will give the right results 🙂

Thanks a lot

[sunny_talwar]

Unfortunately, I don't understand this. Would you may be able to give another shot if this has not already been resolved