Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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...
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..
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
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
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