Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ramzi-manoubi
Partner - Creator
Partner - Creator

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({<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.

1 Solution

Accepted Solutions
sunny_talwar

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
)
)

View solution in original post

3 Replies
sunny_talwar

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
Partner - Creator
Partner - Creator
Author

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
Partner - Creator
Partner - Creator
Author

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
)
)