Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Karen_Gumerov
Contributor III
Contributor III

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 (3)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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
Contributor III
Contributor III
Author

Hi Sunny,

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