topic Re: Set Analysis with Dates - Previous Months based on current date in QlikView

Set Analysis with Dates - Previous Months based on current date

Thu, 02 Apr 2015 10:43:25 GMT

I'm trying to get results based on a current date (YTD, most recent full Month/Quarter etc.) using the following...

Set vCurrentDate= Date('22/02/2015', 'DD/MM/YYYY') // This would not be hard coded but as this is an example it serves the purpose.

With the following Set Analysis equation I get the desired result

='Current Month when date Hard Coded : ' & MaxString({< Date={'22/02/2015'} >} Date) // Returns 22/02/2015

But this just returns the maximum result stored in the calendar.

='Current Date using vCurrentDate : ' & MaxString({ <Date={"=$(vCurrentDate)"}> } Date)

I've tried various formats and achieved nothing. i.e. Date={'$(vCurrentDate)'} etc.

See attached App for issues and CSV used for Dates

Re: Set Analysis with Dates - Previous Months based on current date

rubenmarin — Thu, 02 Apr 2015 10:55:13 GMT

Hi Colin, if you create your variable as:

=Date('22/02/2015', 'DD/MM/YYYY')

(Starting with equal sign)

You can use this expression:

='Current Date using vCurrentDate : ' & Date(Max({ <Date={'$(=vCurrentDate)'}> } Date))

Re: Set Analysis with Dates - Previous Months based on current date

Thu, 02 Apr 2015 10:57:48 GMT

Hi,

Refer this file.

Regards

Vimlesh

Re: Set Analysis with Dates - Previous Months based on current date

Thu, 02 Apr 2015 11:02:28 GMT

Thanks Ruben,

It didn't work but I added one more $ and bracket in it and it is working.

='Current Date using vCurrentDate : ' & Date(Max({ <Date={'$(=$(vCurrentDate))'}> } Date))

Re: Set Analysis with Dates - Previous Months based on current date

Thu, 02 Apr 2015 11:06:04 GMT

Thanks Vimlesh, this looks really good.

Might take a bit of time to review it all

Re: Set Analysis with Dates - Previous Months based on current date

rubenmarin — Thu, 02 Apr 2015 11:30:30 GMT

Ok, just to comment, I didn't copy the right expression!!:

For variable:

=Date('22/02/2015', 'DD/MM/YYYY')

Expression:

='Current Date using vCurrentDate : ' & Date(Max({<Date={'$(vCurrentDate)'}> } Date))

Re: Set Analysis with Dates - Previous Months based on current date

Thu, 02 Apr 2015 11:35:35 GMT

Thanks again Ruben. When I use your code I get "Error in set modifier ad hoc element list: ',' or ')' expected"

Below works

='Current Date using vCurrentDate : ' & Date(Max({<Date={'$(=$(vCurrentDate))'}> } Date))

Re: Set Analysis with Dates - Previous Months based on current date

Thu, 02 Apr 2015 12:37:44 GMT

Vimlesh.. Excellant samples for Set Analysis. - Ram

Re: Set Analysis with Dates - Previous Months based on current date

rubenmarin — Fri, 03 Apr 2015 11:21:26 GMT

Hi Colin, just to explain the difference... that happens if you didn't add the first equal sign in the variable value, with the equal sign the expression is expanded like:

='Current Date using vCurrentDate : ' & Date(Max({<Date={'22/02/2015'}> } Date))

Without the equal is trying something like:

='Current Date using vCurrentDate : ' & Date(Max({<Date={'Date('22/02/2015', 'DD/MM/YYYY')'}> } Date))

And that has a lot of extra quotes, bringing the error.

Your last expression works because you're adding the equal before the variable expansion and a double step expansion:

First step:

='Current Date using vCurrentDate : ' & Date(Max({<Date={'$(=Date('22/02/2015', 'DD/MM/YYYY'))'}> } Date))

Second step:

='Current Date using vCurrentDate : ' & Date(Max({<Date={'22/02/2015'}> } Date))

Re: Set Analysis with Dates - Previous Months based on current date

Fri, 03 Apr 2015 11:33:15 GMT

Thanks for the explanation Ruben.

I set the variable in the Script so didn't think I needed to worry about it again (and thus dismissed your initial comment about adding =). See that you've updated it in the Variable Overview Editor and added the = there.

Would I be right is saying that the difference between my version and your version is that QlikView is treating my version (Date('22/02/2015', 'DD/MM/YYYY') ) as a string and your version (Date('22/02/2015', 'DD/MM/YYYY')) as a number?

i.e. ='vCurrentDate : ' & $(vCurrentDate) returns "vCurrentDate : 0.00549..." and

='vCurrentDate : ' & '$(vCurrentDate)' returns "vCurrentDate 22/02/2015"? I'd probably have expected to see 42057.

Thanks for taking the time with this.

Re: Set Analysis with Dates - Previous Months based on current date

rubenmarin — Fri, 03 Apr 2015 14:51:52 GMT

Hi Colin, transforming each expression:

='vCurrentDate : ' & $(vCurrentDate) is transformed as ='vCurrentDate : ' & 22/02/2015 <-- This is a division: 22 / 2 = 11 / 2015 = 0.00549...
Following operators priority division and multiplication are executed before addition and substration, and the '&' used for concatenate strings is treated as an 'addition' of strings, so first it does the division and then the contenation of the result as a string

='vCurrentDate : ' & '$(vCurrentDate)' is transformed as ='vCurrentDate : ' & '22/02/2015' <-- This is a concatenation of two strings

='vCurrentDate : ' & Num('$(vCurrentDate)') is transformed as ='vCurrentDate : ' & Num('22/02/2015') <-- QV is smart enough to interpret the string as a date and returns it's numerical value of 42057

Re: Set Analysis with Dates - Previous Months based on current date

rubenmarin — Fri, 03 Apr 2015 14:55:59 GMT

Just one detail, for other users loking for the same question, the answer you marked as correct is the one I pasted wrong.

Re: Set Analysis with Dates - Previous Months based on current date

Fri, 03 Apr 2015 15:13:08 GMT

Updated, thanks again!

If it looks like a duck....