topic Re: Set Analysis with 2 Conditions in App Development

Set Analysis with 2 Conditions

kdaniels-obrien — Sat, 16 Nov 2024 17:40:40 GMT

Hi,

I am trying to use a set expression to count the number of orders closed today. I have read through other community posts but was unable to find a solution that worked for me. I am having some syntax issues but I have very limited experience with set expressions so I'm not sure what I'm doing wrong. Here is what I have now:

Count(DISTINCT {<[OrderStatus] = {"Closed"}, timestamp(LastActivity, 'MM/DD') = timestamp(vToday , 'MM/DD')>} Order)

I want to count the distinct orders where OrderStatus = Closed and LastActivity date = a variable vToday. I have to do some timestamp formatting to get LastActivity and vToday to have matching formats. The error says error in set modifier. Is anyone able to tell me what is wrong with my expression/how to fix it?

Note: I use a variable vToday because there are some business cases where vToday does not equal Today()

Re: Set Analysis with 2 Conditions

MayilVahanan — Wed, 23 Dec 2020 05:13:47 GMT

HI @kdaniels-obrien

You can change the format in the script itself

Date(LastActivity, 'MM/DD') as LastActivity

in front end:

Count(DISTINCT {<[OrderStatus] = {"Closed"}, LastActivity = {"$(=Date(vToday , 'MM/DD'))"}>} Order)

but, you didn't include year in ur lastactivity. Is it ur requirement??

Re: Set Analysis with 2 Conditions

kdaniels-obrien — Wed, 23 Dec 2020 13:42:13 GMT

I use the LastActivity field in other places in my app so I don't really want to change it in the load script. Is that the only way this will work?

I probably should include year, I think I just forgot.. 😁