Count based on past due date

[Lori1219]

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

[Digvijay_Singh]

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,

[Digvijay_Singh]

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

[Lori1219]

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

[Digvijay_Singh]

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

[Lori1219]

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

[Lori1219]

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

No longer getting errors.  Thank you