Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Context: CRM, price registration
Used fields:
Price (= price)
Last_Reg (Order of the registration 1 = 1st = most recent registration, 2 = 2nd = 2nd most registration
Prod (= product)
Cust (= customer = store)
When visiting stores, sales reps register the price.
I want to use following formula to find for a certain type of shop the average selling price for a product.
In the If structure I check if this is the most recent registration, taking in account the active filters.
If it is, we take in account the price, else not.
Formula
Avg({<Price={">0"}>} | |
Aggr( | |
if( | |
Min(Last_Reg) = Min(total<Prod,Cust>Last_Reg), Price, 0 | |
), Cust, | |
Prod | |
) | |
) |
When I use this formula in a table with columns: prod, cust, price and this formula,
I expect when there's a price registered, an average equal to the price and yet, there are some for which it is null.
I don't understand how this is possible.
If I need to clarify something, please ask. Any help/ideas is appreciated. Thanks.
You mentioned three dimensions in your table, but used only two in your Aggr()? May be add price too?
Avg({<Price={">0"}>} |
Aggr( |
if( |
Min(Last_Reg) = Min(total<Prod,Cust>Last_Reg), Price, 0 |
), Cust, |
Prod, Price |
) |
) |
You mentioned three dimensions in your table, but used only two in your Aggr()? May be add price too?
Avg({<Price={">0"}>} |
Aggr( |
if( |
Min(Last_Reg) = Min(total<Prod,Cust>Last_Reg), Price, 0 |
), Cust, |
Prod, Price |
) |
) |
Thank you for your the answer, it's much appreciated.
I tried it out and indeed there are no more null values, but the average is no longer only taken on the last registrations.
Got it working, thank you again. Works, whithout the else clause.
Final solution:
Avg({<Price={">0"}>}
Aggr( |
if( |
Min(Last_Reg) = Min(total<Prod,Cust>Last_Reg), Price |
), Cust, |
Prod, Price |
) |
) |