Date range in expression

[gerhardl]

Hi,

I am uploading daily files with one of the fields being "Effective Date".

At the moment I have expressions like these:

Sum ({$<[Effective Date]={"2011/04/10","2011/04/09","2011/04/08"},[Tran Code]={"35"}>}[Trans Amount])

Can someone help me to put a "date-range" instead of the {"2011/04/10","2011/04/09","2011/04/08"}, so in this example it would be all dates from the 8th of April to the 10th of April.

Thanks,

G

[Oleg_Troyansky · Accepted solution]

something like this:

Sum ({$<[Effective Date]={"<=2011/04/10>=2011/04/08"},[Tran Code]={"35"}>}[Trans Amount])

or, more parameter-driven:

Sum ({$<[Effective Date]={"<=$(varFromDate)>=$(varToDate)"},[Tran Code]={"35"}>}[Trans Amount])

where both variables are assigned the formatted date strings

Side comment: I think you misunderstand the role of double quotes in SeT Analysis. Double quotes need to be used to signify search in a field, not individual values. So, if your [Tran Code] is numeric, no quotes are necessary, if it's a string, then single quotes are needed. You'd use double quotes if you wanted to search, for example, using a wildcard "35*"

[sudipguha]

What would be the syntax if I wanted only 1 date :

Sum ({$<[Effective Date]={"Today()"},[Tran Code]={"35"}>}[Trans Amount])

S

[Oleg_Troyansky]

You don't need double quotes (it's not a search if you only need a single value), but you need a $() to get the value of today():

Sum ({$<[Effective Date]={$(=Today())},[Tran Code]={35}>}[Trans Amount])