topic Re: Count based on past due date in App Development

Count based on past due date

Lori1219 — Fri, 18 Feb 2022 19:56:54 GMT

In my source data records there is a due date field. I want to create a very simple visual showing count of records with a due date that is older than today...meaning that it is now past due.

Here's the script I created which throws a garbage error.

if([SPPAP Due Date]<=(today()),count(DISTINCT([SPPAP Due Date])))

Here's the script that an experienced coworker created which also throws a garbage error

COUNT({<[SPPAP Due Date] = {“<= $(=Today())”}>} DISTINCT [SPPAP Due Date])

Re: Count based on past due date

Digvijay_Singh — Fri, 18 Feb 2022 20:19:05 GMT

I prefer set expression but sometimes it doesn't work if comparison is not proper, try to match format of Due date and today -

COUNT({<[SPPAP Due Date] = {“<= $(=Date(Today(),'MM/DD/YYYY'))”}>} DISTINCT [SPPAP Due Date])

Or with single quote may be, don't remember if its needed.

COUNT({<[SPPAP Due Date] = {“<= '$(=Date(Today(),'MM/DD/YYYY'))'”}>} DISTINCT [SPPAP Due Date])

IF will be slower but comparison is straigthforward -

count(distinct if([SPPAP Due Date]<=today(),[SPPAP Due Date]))

pl share some sample data, in case it doesn't work

Thanks,

Re: Count based on past due date

Digvijay_Singh — Fri, 18 Feb 2022 20:20:09 GMT

Also try to see how $() expression is evaluated in the expression editor bottom section, it gives some hint about the problem.

Re: Count based on past due date - here's the error message it's now throwing

Lori1219 — Fri, 18 Feb 2022 21:08:23 GMT

Used this

COUNT({<[SPPAP Due Date] = {"<= '$(=Date(Today(),'MM/DD/YYYY'))'"}>} DISTINCT [SPPAP Due Date])

Got this
[cid:image001.jpg@01D824DE.C41093B0]
Error in expression
Error in set modifier ad hoc element list: " or ' expected
COUNT({<[SPPAP Due Date] = {"<='02/18/2022'"}>}DISTINCT [SPPAP Due Due})

Re: Count based on past due date - here's the error message it's now throwing

Digvijay_Singh — Fri, 18 Feb 2022 21:38:48 GMT

not sure why '}' is in the error expression u mentioned? but not in the first line of your response, is it a typo?

COUNT({<[SPPAP Due Date] = {"<='02/18/2022'"}>}DISTINCT [SPPAP Due Due})

Did you try without date or the exp with IF?

COUNT({<[SPPAP Due Date] = {"<= $(=Today())"}>} DISTINCT [SPPAP Due Date])

Re: Count based on past due date

Lori1219 — Fri, 18 Feb 2022 21:54:23 GMT

Error cleared. Now I need to feed in a selection to get an actual number.
Thank you

Re: Count based on past due date

Lori1219 — Thu, 24 Feb 2022 13:21:22 GMT

COUNT({<[SPPAP Due Date] = {"<= $(=Today())"}>} DISTINCT [SPPAP Due Date])

No longer getting errors. Thank you