topic Date range in expression in QlikView

Date range in expression

gerhardl — Wed, 13 Apr 2011 16:19:32 GMT

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,

Date range in expression

Oleg_Troyansky — Wed, 13 Apr 2011 16:34:31 GMT

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*"

Date range in expression

sudipguha — Thu, 18 Aug 2011 11:43:37 GMT

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

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

Date range in expression

Oleg_Troyansky — Fri, 19 Aug 2011 16:21:33 GMT

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])