14 Replies Latest reply: Jan 23, 2017 3:22 PM by Duke Luke RSS

    Ignoring Set Analysis when user makes selections

    Duke Luke

      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
          Anna fuksa

          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
            Stefan Wühl

            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
                Duke Luke

                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
                  Duke Luke

                  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
                    Duke Luke

                    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:

                     

                    pic.png

                     

                     

                    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
                        Stefan Wühl

                        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
                            Duke Luke

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