Relational operators as set modifiers

[liviumac]

hello

I have the following date structure: date, client, sales

suppose I want to calculate the sales for the period after a certain date (let's say after 20 nov 2016)

I have tried to use sum({<date>42694>}Sales) but it does not work, so is there an alternative way to do this?

thank you very much

LM

[marcus_sommer · Accepted solution]

The syntax isn't quite right. Try this one:

sum({< date = {">42694"} >} Sales)

- Marcus

[Anil_Babu_Samineni]

How is your date format looks like? Is that is in number format or Date format. If date format it won't work for numbers.

date(date) as date

Expression:

sum({< date = {"> $(=Date(...))"} >} Sales)

[sam_grounds]

Hi,

Marcus' answer will give you exactly that.

To expand on this a little bit, it depends on what your data format is. If your date is stored as a number then 42694 will work, however if it is stored as a date, then you will need to do it like this...

sum({$<date={'>20/11/2016'}>}Sales)

(note: use single quotes around the date.)

Or if you're using a dynamic date, something you want the user to be able to specify, the simply pre-calculate a variable with something like this...

sum({$<date={'>$(vDate)'}>}Sales)

The $(vDate) would reference a variable called vDate that you can put into an on-screen object and allow them to change or you can edit it directly with the variable overview (CTRL + ALT + V), or specify it in the load script with...

LET vDate = '20/11/2016';

Or even

LET vDate = Date(Today()-1,'DD/MM/YYYY');

(for yesterday).

If it's numerical format, then use Date#().

Hope this helps.

Sam

[liviumac]

thank you all, your solutions worked - I obviously wasn't using the correct syntax and on top of that I also had a different formatting of the date.

great community, thank you once again.