Set analysis, missing data

[ramzi-manoubi]

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.

[sunny_talwar · Accepted solution]

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

)

)

[ramzi-manoubi]

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.

[ramzi-manoubi]

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

)

)