Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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])
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,
Also try to see how $() expression is evaluated in the expression editor bottom section, it gives some hint about the problem.
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])
COUNT({<[SPPAP Due Date] = {"<= $(=Today())"}>} DISTINCT [SPPAP Due Date])
No longer getting errors. Thank you