Variables in QlikView chart expresssion

[#1]

Hi,

I am currently revising a QlikView file where the previous developer used the following expression to show the number of sales orders with a delivery date earlier than and including November 31st:

Sum({<Year=, Quarter=, Month=, [Delivery date]={'<=2014-11-31'}>} SalesOrders)

It works fine, but I need to automate the process so it doesn't need to be changed every month. I have then created, in the load script, a variable named ThisMonthEnd. I have tried showing it in a TextBox by using the =$(ThisMonthEnd) syntax, and it works fine and displays '2014-11-31'.

But - I can't it to work in an chart expression. I tried this:

Sum({<Year=, Quarter=, Month=, [Delivery date]={'<= $(ThisMonthEnd)'}>} SalesOrders)

and also this

Sum({<Year=, Quarter=, Month=, [Delivery date]={'<=' & $(ThisMonthEnd)}>} SalesOrders)

but it seems the variable doesn't get translated into the date-value. Can anyone help me a bit on this?

Regards, Jan.

[#2]

Hi Pradeep,

=Month($(ThisMonthEnd)) said 'dec', which is a bit weird as I was expecting it to say 'nov' (2014-11-31). I cannot share the application, but I will try to make a small demo QlikView file. I will try to see if I can get it done by later today.

[rubenmarin]

Hi Jan, have you tested variable value in a textbox?

If ThisMonthEnd is defined like "=MonthEnd(Today())", you can create a textbox with "=ThisMonthEnd".

In my case it shows 31/11/2014 30/11/2014 and "=Month(ThisMonthEnd)" returns 'nov'

How is defined ThisMonthEnd variable?

Edit:As Aaron said november has only 30 days, i wrote it wrong

[#4]

Hi Ruben,

Yes, I have tested it in a Textbox. Funny thing is that it says 'dec' where it should return 'nov' for the date 2014-11-31. The format, in the load script, is set to SET DateFormat='YYYY-MM-DD'; so I would expect it to acknowledge that the month values is 11 (November). If I simply put in =$(ThisMonthEnd) in a Textbox by itself, it says 2014-11-31.

But as I wrote above: I will make a small demo file later today.

[morganaaron]

Hi Jan,

Not to ask a stupid question but, November only has 30 days - so 2014-11-31 isn't a legitimate date - how are you creating that date? Maybe that's the source of the problem?

[jonathandienst · Accepted solution]

Hi

Jan Kristensen wrote:

Yes, I have tested it in a Textbox. Funny thing is that it says 'dec' where it should return 'nov' for the date 2014-11-31.

thats because $(ThisMonthEnd) expands to 2014-11-31, which Qlikview sees as an arithmetic expression which evalates to 1972. It then applies the month function to the date value 1972.

Define ThisMonthEnd like this:

     =Date(MonthEnd(Today()), 'YYYY-MM-DD')

(the = must be part of the variable definition)

Then use it like this:

Sum({<Year=, Quarter=, Month=, [Delivery date]={'<= $(=ThisMonthEnd)'}>} SalesOrders)

(notice the extra = sign)

HTH

Jonathan

[vardhancse]

the condition given there was Sum({<Year=, Quarter=, Month=, [Delivery date]={'<=2014-11-31'}>} SalesOrders)

<= and so no issue what ever it may be

[morganaaron]

I was stating that as it wasn't generating a true date, it can't have been interpreting it that way - I think Jonathan states it better (with an actual explanation) above!

[#9]

The end date of 2014-11-31 was of course wrong. I have now managed to get it going by using your MonthEnd suggestion and dropping it directly into the expression like this:

Sum({<Year=, Quarter=, Month=, [Delivery date]={"<=$(=MonthEnd(Today()))"}>} SalesOrders)

I will then do the same to the others, and introduce the MonthStart as well - apparantly you can use an extra parameter to slide forwards/backwards (ie. -1 for the previous month's start).

Thank you to all of you who helped me troubleshoot this - greatly appreciated!