topic Using Set Analysis for Date Ranges...What to do when date is Selected??? in QlikView

Using Set Analysis for Date Ranges...What to do when date is Selected???

prees959 — Tue, 24 Jul 2018 08:33:24 GMT

Hi,

I have three textboxes showing totals. Im using set analysis to show the total for the previous full week in the first box, previous full month in the second and previous 3 full months in the third.

This is working as I'd like but I'm not sure what do display in these boxes when the user selects a certain date, month or year etc from the date filters.. Can anyone suggest anything? For example if the user selects 2017 from the year filter all these boxes show '0' whereas I'd like to be able to show something .....

Sorry for the open ended question 😉

Hope you guys can offer some thoughts..

Phil

Re: Using Set Analysis for Date Ranges...What to do when date is Selected???

petter — Tue, 24 Jul 2018 09:32:13 GMT

Using set expressions is the way to go to achieve what you want.

You can override the current selections totally by using for instance this:

Sum( {1} Sales ) - the 1 here is a so-called set identifier - signifying all the records and all the values without any user-selections applied.

Then you can add some selections like your date-selections similar to this by using set modifiers:

Sum( {1<Year={2016},Month={1,2,3}>} Sales ) - will disregard any selections but apply a new selection via the set modifiers bracketed by the chevrons or angle-brackets < and >. The selections in this case is quite obvious. Year 2017 and the Q1 of that year.

Re: Using Set Analysis for Date Ranges...What to do when date is Selected???

petter — Tue, 24 Jul 2018 09:37:21 GMT

Instead of overriding all current selections you can remove specific field selections from the current selection by doing this:

Sum( {$<Year=>} Sales ) - this disregards the Year selection entirely but keeps whatever other user-selections that might be currently selected.

BTW the $ is the set identifier for the current selection which is also optional so the above expression could be written like this:

Sum( {<Year=>} Sales ) - Note: after the equals sign there is nothing and then the set modifier part of this set experssion ends with the ending angle bracket.