How to sum count of sales by date with condition on count?

[Karen_Gumerov]

Hello,

Please help me to overcome this issue. I've got a chart showing count of sales by date and a threshold value. So I need to sum all the counts of sales which are lower than the threshold in Total Loss KPI. I have already tried tons of things, but nothing helped.

I used formula
Sum({aggr(count(product_id), date) < Aggr(NODISTINCT Avg(Aggr(Count(product_id), product_id, date)), product_id) - 0.1*Aggr(NODISTINCT STDEV(Aggr(Count(product_id), date)), product_id)} aggr(count(product_id), date))

Here

• aggr(count(product_id), date) - count of sales by date
• Aggr(NODISTINCT Avg(Aggr(Count(product_id), product_id, date)), product_id) - 0.1*Aggr(NODISTINCT STDEV(Aggr(Count(product_id), date)), product_id) - threshold value
  
• aggr(count(product_id), date) - expression to be computed
  

This way doesn't work. Could anyone give me a clue how to solve this?
Thanks a lot in advance.
Please find the attached sample qvf.

[sunny_talwar · Accepted solution]

Are you hoping to get 8 for Total Loss KPI? If you do, try this

Sum(Aggr(
If(Count(product_id) < (Aggr(NODISTINCT Avg(Aggr(Count(product_id), product_id, date)), product_id) - 0.1*Aggr(NODISTINCT STDEV(Aggr(Count(product_id), date)), product_id)), Count(product_id))
, date))

[Karen_Gumerov]

Hi Sunny,

This seems to be what I was looking for! Thank you for fast reply!