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: 
jakobjensen
Contributor II
Contributor II

Count how many IDs have a sum(value)<100 between two dates

I have some fields, "ID", "orderdate" and "value".

I want to count, how many IDs have a total sum less than 1000 within the last (chosen) year.

I have tried the following

count({<id={"=sum({<orderdate.autoCalendar.Date={$(= '>=' & addmonths(orderdate.autoCalendar.Date,-12) & '<=' & orderdate.autoCalendar.Date)}>} value)<1000"}>} distinct id)

but it just returns "0" all the time.

Can some one explain why? 

 

 

 

 

 

 

4 Replies
sunny_talwar

Do you have one date selected when you are trying to look at this? If not, may be you need to use Max(Date) like this

Count({<id = {"=Sum({<orderdate.autoCalendar.Date = {""$(='>=' & AddMonths(Max(orderdate.autoCalendar.Date), -12) & '<=' & Max(orderdate.autoCalendar.Date))""}>} value) < 1000"}>} DISTINCT id)

Also, do you need <100 or <1000, please fix accordingly

jakobjensen
Contributor II
Contributor II
Author

Yes, a date is selected.

 

The value doesn't really matter as such, it is just for the sake of demonstrating the issue

sunny_talwar

Got it, did your try this?

Count({<id = {"=Sum({<orderdate.autoCalendar.Date = {""$(='>=' & AddMonths(Max(orderdate.autoCalendar.Date), -12) & '<=' & Max(orderdate.autoCalendar.Date))""}>} value) < 1000"}>} DISTINCT id)

 

jakobjensen
Contributor II
Contributor II
Author

It very much seems like it does! 

Two questions:

1) How come the two double-quotes are needed in the date expression?

2) Can it be extended to the sum being in an interval (e.g 100-200)?