Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problem with Set Analysis and date

Hi all,

I'm trying to build a calendar where I can select dates, and these dates should then change a graph for me.

How ever, what I ever do it just wont work... I'm quite new to Qlikview also, so knowing exactly the fault is difficult to find.
I've googled and search here and still can't figure it out.

Step one has been to make Calendar Objects linked to variables. Check and done. Simple.

The problem is when I'm trying to get the output in a graph.

This is the expression

=Sum({< LastTravelDate = {'>=(=Date(vStartDate))<=(=Date(vEndDate))'} >} NumOrders)

Even if I try and slim in down and fail search:

=Sum({< LastTravelDate = {=vPeriodStart1)} >} NumOrders)

Just wont work, the Variable turns gray, but my NumOrders wont go red...

12 Replies
Not applicable
Author

Thank you, it works! I really appreciate the help!

How about if I want an interval of dates and the number of orders?

=Sum({<LastTravelDate= {">=$(=date(vStartDate,'YYYY-MM-DD'))<=$(=date(vEndDate,'YYYY-MM-DD'))"}>} NumOrders)

I tried this one which I found online, but it't not functioning..

sunny_talwar

The problem is that your dates are not read as date by QlikView. See how they are left aligned, if these were dates, they would have been right aligned

Capture.PNG

You can change this in the script in couple of ways

1) Change the environmental variables

SET DateFormat='YYYY-MM-DD';

2) Fix the field using Date#() function

Date(Date#(LastTravelDate, 'YYYY-MM-DD'), 'YYYY-MM-DD') as LastTravelDate

Read about loading dates in QlikView and things to take care

Why don’t my dates work?

Get the Dates Right

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Enclose your $-sign expansions in single quotes - for the same reason I explained above:

=Sum({<LastTravelDate= {">='$(=date(vStartDate,'YYYY-MM-DD'))'<='$(=date(vEndDate,'YYYY-MM-DD'))'"}>} NumOrders)


Now, you might have a clash between the quotes in your Date format and the quotes around the $-sign expansion...

The better way of resolving it is using Advanced Search instead of a simple search - then you don't have to worry about formatting the dates and about $-sign expansions alltogether. Something like this:


=Sum({<LastTravelDate= {"=LastTravelDate>=vStartDate and LastTravelDate<=vEndDate"}>} NumOrders)


I also noticed that your default Date format is D.M.YYYY. In this case, why use a different format in your Set Analysis conditions? Why not apply the same format everywhere? Anyway, the Advanced Search (double quotes with an equal sign in front) solves all these problems.


cheers,

Oleg Troyansky

Learn advanced QlikView and Qlik Sense techniques in my book QlikView Your Business