topic syntax for date format within set analysis in QlikView

syntax for date format within set analysis

mellerbeck — Wed, 31 Aug 2011 14:34:35 GMT

I've noticed with set analysis that if using a date it of course needs to match exactly if for example you are using an input box to set a variable if you type 08/05/2011 versus 8/5/2011 one will work and the other won't.

So what is the correct syntax for a date format within set analysis, or is there a better (different?) way?

Sum({$<Tran_date = {"$(vNewDate)"}>}

It tried

Sum({$<Tran_date = {"date($(vNewDate),'MM/DD/YYYY')"}>}

but that doesn't seem to work.

syntax for date format within set analysis

mellerbeck — Wed, 31 Aug 2011 14:54:02 GMT

Gah, got it of course after posting although a review wouldn't hurt my feelings

Sum(

{$<

Tran_date = {"$(=date(vNewDate,'MM/DD/YYYY'))"}>}

syntax for date format within set analysis

Wed, 31 Aug 2011 22:23:54 GMT

I tend to convert my dates into numbers, and do comparisons and set analysis that way. Set analyisis with dates has bit me more than once.

syntax for date format within set analysis

johnpaul — Thu, 01 Sep 2011 02:03:12 GMT

I use variables for both if possible:

Sum ({$<Tran_Date = {">=$(vDateStartLY)<=$(vDateEndLY)"}>}Invoice)

Part of the reason for this is that we support systems outside of the US as well as the US, so the date formats need to be considered.