Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Variables in QlikView chart expresssion

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.

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

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

17 Replies
vardhancse
Specialist III
Specialist III

try like:

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

datanibbler
Champion
Champion

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

Not applicable
Author

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
Specialist III
Specialist III

For the Delivery Date part try this:

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

vardhancse
Specialist III
Specialist III

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

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

Not applicable
Author

They are blank (not selected).

vardhancse
Specialist III
Specialist III

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

Not applicable
Author

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
Specialist II
Specialist II

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?