topic Re: Ignoring Set Analysis when user makes selections in QlikView

Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 16 Jan 2017 15:13:00 GMT

Hi, I was just wondering if it's possible for a user to make a selection that disregards set analysis. For example, I currently have two charts side-by-side for comparative purposes. I have used alternate states so that selections made with certain list boxes will affect only one chart. These charts are both defaulted with their monthly view being a 12 month rolling (using set analysis) and 12 week rolling (using set analysis) depending upon a button click by the user. However, when I make a selection that falls outside of set analysis, for example I choose April, 2016, when used in the rolling 12 weeks, I get "No data to display". How can I use set analysis to limit what the user sees (so that they are not overwhelmed with data and have to scroll) but ignore set analysis when user makes selection.

for example:

=count({<[Dummy1] = {'0'}, dummy2 = {'1'}, Dummy3 = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)

+

count({< Dummy4 = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} [Control Number])

Re: Ignoring Set Analysis when user makes selections

annafuksa1 — Mon, 16 Jan 2017 15:15:42 GMT

use in set analysis 'Month=' or 'Week='

for example

sum({Month=, Week=} Sales)

that way it will ignore any selection in Month and Week fields

Anna

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 16 Jan 2017 15:18:00 GMT

Hi anna. The issue I'm having is that my set analysis limits data to 12 month rolling or 12 week rolling. For example 12 month rolling shows all the way from December 2015 - December 2016. What if user want to make selection from April 2015. This is showing "No data to display" because of set analysis I believe. I would still like dimension limited to 12 month rolling using set analysis when end user makes no selection, but when they make a selection to disregard set analysis and let them freely choose.

Re: Ignoring Set Analysis when user makes selections

MK_QSL — Mon, 16 Jan 2017 15:19:35 GMT

What expression you are using?

Re: Ignoring Set Analysis when user makes selections

swuehl — Mon, 16 Jan 2017 15:21:34 GMT

You can check if the user made a selection using e.g. GetSelectedCount( FieldName ), then branch into different aggregations with / without set analysis:

=If(GetSelectedCount( Year ) >0,

Sum( Value ), // no set analysis, consider user selection

Sum( {<Year = {2017} >} Value) // set analysis default year

)

Field Functions

Re: Ignoring Set Analysis when user makes selections

annafuksa1 — Mon, 16 Jan 2017 15:26:17 GMT

it will be for example

sum({<Date= (">$(vRollingMonth)"), Month=, Week=>} Sales)

you can use the same field twice if you need to.

can you paste your formula or an app ?

Anna

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 16 Jan 2017 15:28:45 GMT

Hi. see above for an example

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 16 Jan 2017 15:28:52 GMT

See above for example

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 16 Jan 2017 15:29:43 GMT

is this really how to do this? this seems like it should be easy to do in qlikview..... is this the only way to accomplish this?

Re: Ignoring Set Analysis when user makes selections

annafuksa1 — Mon, 16 Jan 2017 15:33:18 GMT

sorry

=count({<[Dummy1] = {'0'}, dummy2 = {'1'}, Dummy3 = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}, Month, week,year>} Pkey)

+

count({< Dummy4 = {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}, Month, week,year>} [Control Number])

this way it will ignore any selection in fields MOnth, Week and Year

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 16 Jan 2017 15:36:19 GMT

Stefan - for example I have Year/Quarter/Month/Week/Day. If user makes any of these selections then it should ignore set analysis correct? it seems this would be tedious with the way you suggested.. It would be easier if I knew the choices the end-user would make.

i.e.

=If(GetSelectedCount( Year ) >0 or

GetSelectedCount( Quarter) >0 or

GetSelectedCount( <Month) >0 or

GetSelectedCount( Week) >0 or

GetSelectedCount( Day) >0 ,

Sum( Value ), // no set analysis, consider user selection

Sum( {<Year = {2017} >} Value) // set analysis default year

)

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 16 Jan 2017 15:47:02 GMT

I'm confused. why would I want to ignore selections for these fields?

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 23 Jan 2017 16:25:51 GMT

Hi Stefan, this is good advice.

My formula is:

=
IF(GetSelectedCount(CanonicalMonth) > 0 OR
GetSelectedCount(CanonicalYear) > 0 OR
GetSelectedCount(CanonicalWeekName) > 0 OR
GetSelectedCount(CanonicalWeekDay) > 0 OR
GetSelectedCount(CanonicalQuarter) > 0,

(count({<[Dummy1] = {'0'}, dummy2 = {'1'}, Dummy3 = {'1'}, DateType = {'Created'}>} Pkey)

+

count({<Dummy4= {'1'}, DateType = {'Created'}>} [Control Number])),

(count({<[Dummy1] = {'0'}, dummy2= {'1'}, Dummy3= {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)

+

count({<Dummy4= {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} [Control Number])))

However, when I use this formula qlikview is defaulting my axis' to what it would be without set analysis, but with only the values of what is included in set analysis, when no selections are made. See this picture for an example:

Do you know how I can default this to be my Set Analysis values when ONLY the selections are made?

Re: Ignoring Set Analysis when user makes selections

swuehl — Mon, 23 Jan 2017 18:50:10 GMT

You are using two expressions , right? And you have shown the one for 'Created', right?

How does the one for 'resolved' look like?

Re: Ignoring Set Analysis when user makes selections

lucasdavis500 — Mon, 23 Jan 2017 20:22:32 GMT

They were all showing incorrectly. However, I found a work-around.

Instead of having this in one expression, I made two expressions. For example:

(count({<[Dummy1] = {'0'}, dummy2 = {'1'}, Dummy3 = {'1'}, DateType = {'Created'}>} Pkey)
+
count({<Dummy4= {'1'}, DateType = {'Created'}>} [Control Number])),

and

(count({<[Dummy1] = {'0'}, dummy2= {'1'}, Dummy3= {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} Pkey)
+
count({<Dummy4= {'1'}, DateType = {'Created'}, CanonicalDate = {$(vTwelveRolling)}>} [Control Number])))

The only difference between these expressions is that one contains A Date Limitation using Set Analysis, and the other does not.

Now, using conditional formatting I can build off of your expressions.

vWeekMonth = 1 AND $(vSADate)

for the first expression

and

vWeekMonth = 1 AND $(vSANoDate)

for the second expressions,

where

$(vSADate) =

(GetSelectedCount(CanonicalYear) = 0 AND GetSelectedCount(CanonicalQuarter) = 0 AND GetSelectedCount(CanonicalMonth) = 0 AND GetSelectedCount(CanonicalWeekName) = 0 AND GetSelectedCount(CanonicalWeekDay) = 0)

and

$(vSANoDate) =

(GetSelectedCount(CanonicalYear) > 0 OR GetSelectedCount(CanonicalQuarter) > 0 OR GetSelectedCount(CanonicalMonth) > 0 OR GetSelectedCount(CanonicalWeekName) > 0 OR GetSelectedCount(CanonicalWeekDay) > 0)

So, for me to be give my end-user the ability to toggle through both a Monthly and Weekly view, plus have set analysis limit each to 12 months and 12 weeks respectively with the ability to make their own date selections, i.e. 3 years of data, or >12 weeks of data, I have to create 4 variations of one expression.

I personally enjoy qlikview, but this seems to be a major limitation. I also find it very surprising that I cannot find any documentation on an end-user having a requirement like this. is it really the case that end-users want to see all their data on the X-AXIS and using the scroll bar, or only showing x number of periods using set analysis, but not being able to have the freedom to make their own selection afterward without doing what I've done above...