14 Replies Latest reply: Feb 2, 2016 12:33 PM by Gysbert Wassenaar RSS

    Using multiple variables in ValueList that are AGG() functions

    Matthew Brenner

      Hello everyone. I'm trying to get a pie chart built in Qlik Sense but I'm having some issues. I've got 2 variables

       

      $(vTest1)

      Sum(Aggr(

      Only({<scheduleTypeId={7}>} scheduleValue) * Only({<metricId={101}>} hourlyValue)

      ,masterCalendarDate,locationId))

       

      $(vTest2)

      Sum(Aggr(

      (Only({<metricId={1}>} hourlyValue) - Only({<scheduleTypeId={7}>} scheduleValue)) * Only({<metricId={102}>} hourlyValue)

      ,masterCalendarDate,locationId))

       

      And I want to put both of their values into a pie chart. I have the following expression as my Dimension:

      ValueList('Test1','Test2')

       

      and my Measure:

      if(ValueList('Test1','Test2')='Test1',$(vTest1), $(vTest2))

       

      With the following tables, I'm only seeing the value for Test1 show up in my pie chart

      locationIdmasterCalendarDatemetricIdhourlyValue
      43

      12/31/2015 19:00

      110
      4312/31/2015 19:001011
      4312/31/2015 19:001029
      4312/31/2015 19:008768

       

      locationIdmasterCalendarDatescheduleIdscheduleValue
      4312/31/2015 19:0075

       

      What I'm seeing is that if I put $(vTest1) and $(vTest2) into KPI I get the correct values (5 and 45). But I'm only seeing $(vTest1) in my pie chart. Here is a SS of my sheet. I'm not sure why the Pie Chart says I have negative or zero values since I can see that both are positive values. Any help or advice you've got is greatly appreciated. Thanks!

      Capture.PNG

        • Re: Using multiple variables in ValueList that are AGG() functions
          Sunny Talwar

          Would you be able to share you application here?

          • Re: Using multiple variables in ValueList that are AGG() functions
            Gysbert Wassenaar

            Try these:

             

            $(vTest1)

            Sum(Aggr(NODISTINCT

            Only({<scheduleTypeId={7}>} scheduleValue) * Only({<metricId={101}>} hourlyValue)

            ,masterCalendarDate,locationId))

             

            $(vTest2)

            Sum(Aggr(NODISTINCT

            (Only({<metricId={1}>} hourlyValue) - Only({<scheduleTypeId={7}>} scheduleValue)) * Only({<metricId={102}>} hourlyValue)

            ,masterCalendarDate,locationId))

              • Re: Using multiple variables in ValueList that are AGG() functions
                Sunny Talwar

                Yup that did it. Can you elaborate on why it wasn't working without NODISTINCT?

                  • Re: Using multiple variables in ValueList that are AGG() functions
                    Gysbert Wassenaar

                    To be honest I haven't got the foggiest . I just figured that using aggr in combination with a synthetic dimension was bound to require some qlikvudu. Oleg will probably say he explains it all in his book and we really really should buy it or attend the Qlik Masters Summit (just kidding Oleg). But perhaps Hernic is willing to explain it for free

                      • Re: Using multiple variables in ValueList that are AGG() functions
                        Oleg Troyansky

                        How did you know, gwassenaar ? (just kidding :-)

                         

                        Sorry, I read it all backwards... so ignore the previous. Here is  the real explanation:

                         

                        AGGR is DISTINCT by default, and by definition is produces a single value for each combination of the dimensions. So, this AGGR will produce one single value for each Location ID and Date.

                         

                        The chart, however, includes a synthetic dimension "Test", which is not covered in the AGGR(). Out of the two chart lines only one line (the first) gets the single produced value and the second line will get a zero. Adding NODISTINCT makes the AGGR performed at each detailed line and produce multiple results for each combination of the dimensions. This is what makes this formula work in this case.

                         

                         

                         

                        However, keep in mind that this fix is not universal! In many situations, NODISTINCT can heavily distort your results. I'd say that in those cases when AGGR() needs to be involved, I'd recommend avoiding synthetic dimensions and replacing them with actual Fields. A Field can be added as the extra dimension to the AGGR() function.

                         

                        And yes, it is discussed in my book, along with demystifying  a lot of other mystery around AGGR. And it is surely part of the Masters Summit curriculum. Come and learn, it looks like even the best community experts would have something new to learn from both!

                         

                        In my book and in my Masters Summit lecture, I call this phenomenon "The third law of AGGR", or "The safe/unsafe use of AGGR() in charts". I dare you to figure out what it means!

                         

                        cheers,

                        Oleg

                          • Re: Using multiple variables in ValueList that are AGG() functions
                            Stefan Wühl

                            I do agree, we always need to remember the default DISTINCTness of AGGR()

                             

                            Issues with Aggr() and synthetic dimensions (but also with data islands) seem to pop-up sporadically since I am on this forum (no other solutions seen than NODISTINCT and sometimes replacing the valuelist() with a data island and adding the data island dimension field to the aggr() dimensions).

                             

                            I also think this has something to do with what Henric called a grain mismatch:

                            Pitfalls of the Aggr function

                             

                            It's just hard to understand why this also happens when the expressions uses in fact two different aggregations with two different aggr() functions (but with same aggr() dimensions used on both) for each synthetic dimension line.

                             

                            Interestingly enough, the issue also appears with only a single aggr() function used, as long it's not appearing in the first dimension line:

                             

                            =if(ValueList('Test1','Test2')='Test1','Constant', $(vTest2)) // no good


                            if(ValueList('Test1','Test2')='Test1',$(vTest2),'Constant') // ok

                             

                             

                            Oleg, I already bought your book, so I need to dig in again to find out why this happens.

                             

                            Matt: In your specific example, I don't think you need the aggr() functions at all, this should return the same output, since your tables are linked by the relevant fields:

                             

                            =Only({<scheduleTypeId={7}>} scheduleValue) * Only({<metricId={101}>} hourlyValue)

                             

                            =(Only({<metricId={1}>} hourlyValue) - Only({<scheduleTypeId={7}>} scheduleValue)) * Only({<metricId={102}>} hourlyValue)

                              • Re: Using multiple variables in ValueList that are AGG() functions
                                Oleg Troyansky

                                Stephan, to simplify your search - look up "The Third Law of AGGR" in my book. It explains how AGGR doesn't perform very well when the AGGR() dimensions are not more detailed than the Chart dimensions.

                                 

                                This issue is really hard to wrap your mind around... A DISTINCT AGGR will produce a single value for each combination of its dimensions. This chart requires two values, but we only have one single value coming back from AGGR(). In order to produce two values, one of the following needs to happen:

                                 

                                1. Either the AGGR needs to include the chart dimension, and for that the chart dimension needs to be a Field, not a calculated formula. This would be an implementation of "The Third Law of AGGR()".

                                 

                                2. Or, if possible, the AGGR can be made NODISTINCT, and then it will return multiple values for each combination of the dimensional values, but in many cases the aggregate results will be incorrect.

                                 

                                cheers,

                                Oleg Troyansky

                                  • Re: Using multiple variables in ValueList that are AGG() functions
                                    Sunny Talwar

                                    But what is confusing here is that if the above worked in a text box object why would it have difficult (and would need NODISTINCT) in a ValueList dimension. I would essentially think that it should behave the same way, no? What's different here?

                                    • Re: Using multiple variables in ValueList that are AGG() functions
                                      Stefan Wühl

                                      Oleg, I think I've understood the grain mismatch issues in general (leading to headache from time to time when building the virtual cubes).

                                      I am still reading the part in your book (p.500-547), though, quite a lot of stuff.

                                       

                                      I am more asking myself why this happens also in this specific setting.

                                       

                                      I think this has something to do with both expressions in each path of the if-statement being executed and evaluated all of the time (no short-circuiting), for both dimensional values, something I remember from a discussion with Henric and Rob:help - performance improvements

                                       

                                      This would explain (to me), why the grain mismatch issue also happens with something like

                                       

                                      =if(ValueList('Test1','Test2')='Test1','Constant', $(vTest2)) // no good

                                       

                                      Sunny, there is a difference between evaluation in a text box and using a chart with dimensions, see Oleg's explanation and HIC's blog post I've mentioned above. The grain mismatch only appears if you have conflicting /mismatching chart dimensions vs. aggr() dimensions.

                                        • Re: Using multiple variables in ValueList that are AGG() functions
                                          Oleg Troyansky

                                          Stephan,

                                           

                                          correct. The "grain mismatch", as HIC calls it, or the "Third Law of AGGR()", as I call it, happens disregarding of the IF() condition and the synthetic dimension.

                                           

                                          What baffles most of us here is that logically, the expression appears to be fine. IF Test1, then give me the single value from this AGGR(), otherwise, give me the single value from the other AGGR(), - but QlikView engine doesn't seem to follow this logic. For QlikView, there is the expression (think of it as a black box) and two dimensional values. The fact that one AGGR() should be used for one dimensional value and the other AGGR() should be used for another dimensional value, is purely coincidental. No matter what, QlikView dispenses the single available result of all AGGR() functions to the first line, and that's it...

                                    • Re: Using multiple variables in ValueList that are AGG() functions
                                      Gysbert Wassenaar

                                      Thanks Oleg! You've confirmed my suspicions

                                • Re: Using multiple variables in ValueList that are AGG() functions
                                  Matthew Brenner

                                  This was the trick, and I'll follow up what Sunny said and ask why we need NODISTINCT here