Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
kgkgkgkg
New Contributor II

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

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.

1234.jpg

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.

Labels (6)
1 Solution

Accepted Solutions

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

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

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

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))
kgkgkgkg
New Contributor II

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

Hi Sunny,

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