topic Re: Variables in QlikView chart expresssion in QlikView

Variables in QlikView chart expresssion

Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Variables in QlikView chart expresssion

vardhancse — Tue, 25 Nov 2014 14:57:07 GMT

try like:

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

Re: Variables in QlikView chart expresssion

datanibbler — Tue, 25 Nov 2014 14:59:58 GMT

Hi,

that looks prettty correct. The set_expression should work like this actually.

Have you checked the format of [delivery_date]? You might have to turn both dates into numeric values?

(with dates, there is often some issue, usually about the format.)

HTH

Best regards,

DataNibbler

Re: Variables in QlikView chart expresssion

Tue, 25 Nov 2014 15:02:49 GMT

Hi Sasi - and thanks for your quick reply.

I tried it, but it still doesn't work. I get the "curly line" indicating an error at the }>} characters after the '$(ThisMonthEnd)'. However, the "Edit Expression" box still says "Expression OK". It just gives me '-' in the column in the chart.

Re: Variables in QlikView chart expresssion

marcus_malinow — Tue, 25 Nov 2014 15:02:50 GMT

For the Delivery Date part try this:

[Delivery date]={"<=$(=ThisMonthEnd)"}

Re: Variables in QlikView chart expresssion

vardhancse — Tue, 25 Nov 2014 15:07:41 GMT

just for clarification what was the value ur pasiing for other fields like year,quarter

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

Re: Variables in QlikView chart expresssion

Tue, 25 Nov 2014 15:08:47 GMT

They are blank (not selected).

Re: Variables in QlikView chart expresssion

vardhancse — Tue, 25 Nov 2014 15:11:37 GMT

whatever fields condition were not required, just remove those fields because its a AND condition as there is no proper data for fields year,quarter & month the result is blank(-)

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

Then it will take sum of sales order where delivery date = variable value

Re: Variables in QlikView chart expresssion

Wed, 26 Nov 2014 07:04:23 GMT

It also doesn't work. Actually, I thought <Year=,Quarter=,Month= was a way of being sure that nothing was selected in year, quarter and month. As I stated in my question, it worked fine in the original version:

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

Re: Variables in QlikView chart expresssion

PradeepReddy — Wed, 26 Nov 2014 07:39:13 GMT

Check the result of the variable 'ThisMonthEnd' is in date format or not? If not in date format convert it to date and try.

Ex: Month($(ThisMonthEnd)), it should give some either month number or month name.

Can you please share the application with some sample data?

Re: Variables in QlikView chart expresssion

Wed, 26 Nov 2014 08:00:38 GMT

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.

Re: Variables in QlikView chart expresssion

rubenmarin — Wed, 26 Nov 2014 08:15:08 GMT

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

Re: Variables in QlikView chart expresssion

Wed, 26 Nov 2014 08:39:16 GMT

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.

Re: Variables in QlikView chart expresssion

morganaaron — Wed, 26 Nov 2014 08:42:57 GMT

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?

Re: Variables in QlikView chart expresssion

jonathandienst — Wed, 26 Nov 2014 09:08:29 GMT

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

Re: Variables in QlikView chart expresssion

vardhancse — Wed, 26 Nov 2014 10:23:37 GMT

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

<= and so no issue what ever it may be

Re: Variables in QlikView chart expresssion

morganaaron — Wed, 26 Nov 2014 10:40:43 GMT

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!

Re: Variables in QlikView chart expresssion

Wed, 26 Nov 2014 14:41:49 GMT

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!