# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Contributor

## Set analysis, missing data

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({0"}>} Aggr( if( Min(Last_Reg) = Min(totalLast_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.

1 Solution

Accepted Solutions
MVP

## Re: Set analysis, missing data

You mentioned three dimensions in your table, but used only two in your Aggr()? May be add price too?

 Avg({0"}>} Aggr( if( Min(Last_Reg) = Min(totalLast_Reg), Price,  0 ), Cust, Prod, Price ) )
3 Replies
MVP

## Re: Set analysis, missing data

You mentioned three dimensions in your table, but used only two in your Aggr()? May be add price too?

 Avg({0"}>} Aggr( if( Min(Last_Reg) = Min(totalLast_Reg), Price,  0 ), Cust, Prod, Price ) )
Contributor

## Re: Set analysis, missing data

I tried it out and indeed there are no more null values, but the average is no longer only taken on the last registrations.

Contributor

## Re: Set analysis, missing data

Got it working, thank you again. Works, whithout the else clause.

Final solution:

Avg({<Price={">0"}>}

 Aggr( if( Min(Last_Reg) = Min(totalLast_Reg), Price ), Cust, Prod, Price ) )
Community Browser