Solved: Variables in QlikView chart expresssion - Qlik Community

Report Inappropriate Content · ‎2014-11-25

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.

jonathandienst · ‎2014-11-26

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

vardhancse · ‎2014-11-25

try like:

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

datanibbler · ‎2014-11-25

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

Report Inappropriate Content · ‎2014-11-25

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.

marcus_malinow · ‎2014-11-25

For the Delivery Date part try this:

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

vardhancse · ‎2014-11-25

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

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

Report Inappropriate Content · ‎2014-11-25

They are blank (not selected).

vardhancse · ‎2014-11-25

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

Report Inappropriate Content · ‎2014-11-26

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)

PradeepReddy · ‎2014-11-26

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?