8 Replies Latest reply: Aug 15, 2012 9:06 AM by Alexander Karlsson RSS

    Sheet-Level Set Analysis?

    Steven Bain

      Hiya Guys,

       

      Is it possible to apply Set Analysis (or similar) to a specific Sheet?

       

      For example, if you had a Dashboard for a market stall which has data on all available fruit and veg - would it be possible to apply an "Apples Only" Set Analysis-style filter on one Sheet - so all previous variables could be used, but applying only to "Apples"?... otherwise I am going to have to create a whole new set of variables just for this one specific sheet - which would be complicated and prone to user-error.

       

      Any information anyone can provide will be greatly appreciated.

       

      Cheers,

       

      Steve.

        • Re: Sheet-Level Set Analysis?
          Steven Bain

          I'm guessing no one knows?!  :-)

           

          I have been looking into the "aggr" function - as this seems to create temporary tables' to work with... but not entirely sure of it's workings.  Does "aggr" ignore current Selections (ie: like {1} within Set Analysis?) or would a current selection effect the data returned?

           

          Hopefully by asking this extra question it will "bump" this post and get more views!!!  :-)

           

          Cheers,

           

          Steve.

          • Re: Sheet-Level Set Analysis?
            Michael Solomovich

            Steve,

             

            There is no relation between current sheet and the data.  Sheet is just a space for the objects.  But - you can retrieve current sheet ID using macro, and store it in a variable.  After that, you can use it any way you want, e.g. as a condition in expressions (set or no set).

             

            Regards,

            Michael

              • Re: Sheet-Level Set Analysis?
                Steven Bain

                Thanks Michael,

                 

                So, that would mean updating all of my Expressions with an "If" statement which will, if the "Apples" Sheet is active, perform Set Analysis for just "Apples" - otherwise use my original Expression?

                 

                Seems quite convoluted (as does everything within QlikView!) - I might have a play with it and see if I can get it to work.  I may also suggest a development to the QV Tech guys which would allow the "filtering" functionality I suggested (above)... would you agree that this would be a useful development in the future?

                 

                On a similar note - is this, effectively, how the "aggr" function works?

                 

                Cheers,

                 

                Steve.

                  • Re: Sheet-Level Set Analysis?
                    Alexander Karlsson

                    Perhaps a OnActivateSheet trigger for the sheet that selects "Apples" for you , lock that selection.

                    Clear selection and unlock the field as a OnLeaveSheet trigger.

                     

                    Not very elegant as it really locks down your users but atleast you don't get the performance hit of introducing a if statement for every expression.

                      • Re: Sheet-Level Set Analysis?
                        Steven Bain

                        Thanks Alexander,

                         

                        As you say - not very elegant, but it would defitely do the trick for Sheet-Level filtering (the problem would be to remember the previous Selection so it could be re-applied when "OnLeaveSheet" triggers).

                         

                        As an aside - what would be the best solution for, for example, a chart just based on just "Apples"? (regardless of the current Selection) - with other Objects being attached to the Selection?  Set Analysis using {1}?  Or possibly the "aggr" function?

                         

                        I'm struggling to see the use of the "aggr" function...

                         

                        Cheers,

                         

                        Steve.

                          • Re: Sheet-Level Set Analysis?
                            Alexander Karlsson

                            Well, you should be able to clear a single field e.g the one containing apple and leaving the other selections intact.

                             

                            Disregarding the current selections with Set Analysis is the way to go atleast performance wise.

                            If you are running QV11 I would also adivse that you look into alternate states. That would allow you to break your application/data into more states then the $ (default state) we are used to and possibly create a very confusing mess for the users .

                             

                            If you wanna force a product selection just do a Sum({<Product = {Apple}>} SalesAmount) but as you say you would have to move away from your variables as this is a deviation from your orginal expression... makes sense though

                             

                            For aggr check out the "Nested Aggregations and Related Issues" chapter in the help file, it describes a very common scenario where aggr comes into play.

                              • Re: Sheet-Level Set Analysis?
                                Steven Bain

                                Thanks again Alexander - very helpful!

                                 

                                When I get chance I'll do some more reading  :-)  It's difficult juggling the balance between learning the best way/how to develop something - and then ACTUALLY developing it!  :-)

                                 

                                I've marked your previous answer as "Correct" - as I think that's going to be as close as we're gonna get!  :-)

                                 

                                Cheers,

                                 

                                Steve.