Set Analysis syntax error

[prma7799]

Hi All,

I am getting syntax error and also not getting sum of quantity for that expression.

PFA...

[tresesco]

What would be the date condition, could you explain in words?

[prma7799]

I have written this code

=sum({<[Expiration Date]={"<=$(=today())>01/01/1753"} [Remaining Quantity])

but i am not getting sum qty less than today date and i dont want to consider  QTY for 01/01/1753 date

[chinnuchinni]

try with this:

=sum({<[Expiration Date]= {'>$(=date(01/01/1753,'DD/MM/YYYY'))<=$(=max([Expiration Date]))'}>}[Remaining Quantity])

[tresesco · Accepted solution]

Try formatting the timestamps similar to the date field like:

=sum({<[Expiration Date]={"<=$(=timestamp(today(),'MM/DD/YYYY hh:mm:mm TT')) >01/01/1753 00:00:00 PM"}>} [Remaining Quantity])

[prma7799]

Hi tresesco‌

when i using you expression in my production application then it is coming 0 value

i have used this

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

[tresesco]

How do the values in [Expiration Date] field look like in prod?

[prma7799]

When i use below then it is working fine

=sum({<[Expiration Date]={"<=$(=timestamp(today(),'DD/MM/YYYY hh:mm:mm')) >01/01/1753 00:00:00 PM"}>} RemainingQuantity)

Expiration Date look like below

[tresesco]

The field format and the comparison parameters have to match. You should also, remove 'PM' from the last value part.