Set Analysis with Dates - Previous Months based on current date

[#1]

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

[rubenmarin]

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

[#3]

Hi,

Refer this file.

Regards

Vimlesh

[#4]

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

[#5]

Thanks Vimlesh, this looks really good.

Might take a bit of time to review it all

[rubenmarin · Accepted solution]

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

[#7]

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

[#8]

Vimlesh.. Excellant samples for Set Analysis. - Ram

[rubenmarin]

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

[#10]

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.