topic Problem with Set Analysis and date in QlikView

Problem with Set Analysis and date

Wed, 25 Nov 2020 16:16:04 GMT

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...

Re: Problem with Set Analysis and date

sunny_talwar — Mon, 24 Oct 2016 15:28:18 GMT

It would be difficult to know what might be missing without seeing a sample. Would you be able to provide one? In the mean time you can also read this blog

Dates in Set Analysis

Re: Problem with Set Analysis and date

Oleg_Troyansky — Mon, 24 Oct 2016 15:31:33 GMT

Hi Kevin,

it would be a lot easier to troubleshoot if you could post a small sample app with your chart...

In the first expression, you should remove the single quotes within the braces:

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

In the second expression, you don't need the equal sign for the variable, but you do need to enclose it in a $-sign expansion and possibly convert to the date format:

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

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

You can learn more about Set Analysis and many other advanced Qlik techniques in my book QlikView Your Business.

cheers,

Oleg Troyansky

Re: Problem with Set Analysis and date

Anonymous — Mon, 24 Oct 2016 15:48:01 GMT

Kevin

As pointed out by others a sample app would make it easier to offer a solution. In the meantime you could always try entering your expression in a text box and playing around with the dates i.e.e selecting a small date range such as two or three days to make it easier to check the results.

Re: Problem with Set Analysis and date

Mon, 24 Oct 2016 18:42:34 GMT

Yes of course, stupid of me, forgetting the file.

I've attached it here.

Re: Problem with Set Analysis and date

Mon, 24 Oct 2016 18:44:44 GMT

Thank you for the reply, I have not attached a sample.

I was working around in a text box, as you mention, easier to check results... and without results..

Re: Problem with Set Analysis and date

sunny_talwar — Mon, 24 Oct 2016 18:56:40 GMT

Is three the correct number?

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

Re: Problem with Set Analysis and date

Oleg_Troyansky — Mon, 24 Oct 2016 19:00:16 GMT

OK, much better now.

Your variable for Start Period contains a formula that returns a formatted Date. For that reason, it needs to be enclosed in a $-sign expansion (because it's a variable) and in a set of single quotes (because it returns a formatted date. So, the corrected formula is:

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

The corrected sample is also attached.

cheers,

Oleg Troyansky

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

Re: Problem with Set Analysis and date

Mon, 24 Oct 2016 19:05:12 GMT

Hi!

Thank you for the help. I see the difference. But if I change the PeriodStart to vStartDate which is linked in the calendar, it wont function. Is there something wrong in the calendar ?

Re: Problem with Set Analysis and date

sunny_talwar — Mon, 24 Oct 2016 19:10:57 GMT

I guess you just need to give it a proper format and it works:

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

Re: Problem with Set Analysis and date

Mon, 24 Oct 2016 19:23:22 GMT

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..

Re: Problem with Set Analysis and date

sunny_talwar — Mon, 24 Oct 2016 20:11:55 GMT

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

Why don’t my dates work?

Get the Dates Right

Re: Problem with Set Analysis and date

Oleg_Troyansky — Mon, 24 Oct 2016 20:12:09 GMT

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