topic Re: Count how many IDs have a sum(value)<100 between two dates in App Development

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

jakobjensen — Fri, 28 Aug 2020 11:39:45 GMT

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?

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

sunny_talwar — Fri, 28 Aug 2020 13:00:44 GMT

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

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

jakobjensen — Fri, 28 Aug 2020 13:12:58 GMT

Yes, a date is selected.

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

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

sunny_talwar — Fri, 28 Aug 2020 13:16:20 GMT

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)

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

jakobjensen — Mon, 31 Aug 2020 07:08:29 GMT

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