9 Replies Latest reply: Feb 17, 2010 11:43 AM by Aaron Witt RSS

    Set Expression Help

    Korin Geri

      Hello

      I have a weird issue - see attached example

      When I do a set expression by fixed value (2009) everything is ok

      When I do a set expression with a condition by the dimension field - it returns 0

       

      Im obviously missing something..

       

        • Set Expression Help
          Corine LEE BAW

          Hi,

          Set analysis works fine with a single value, in your example "GoalYear=SaleDateY", QlikView does not know with which valie to campare "GoalYear" even though "SalesdateY" has 29 times the same value "2009".

          =SUM(if(GoalYear=SaleDateY,GoalQuantity,0)) would give you a value but is not good regarding performance issues.

          A solution would be to be able to create a flag in your load script and use the flag to calculate your expression.

           

          C. Lee Baw

          Business & Decision

            • Set Expression Help
              Korin Geri
              Hi Why it doesnt know which values to sum up? Each cell in the pivot corresponds to certain dimensions in the pivot, so what's the difference (from QV point of view) between the expression which you wrote, and the SET ANALYSIS one? Thanks
            • Set Expression Help

              Hi,

              I changed to

              =SUM({$<GoalYear={$(=SaleDateY)}>}GoalQuantity)
              and the result is what you expect

              The magic of set analysis syntaxis!

              Pierre.

                • Set Expression Help
                  Korin Geri

                  Hi

                  It does work :)

                  1. Could you please explain the syntax? Since I dont seem to understand why what I did was wrong according to QV's documentation

                   

                  2. Actually I need to do this by more then 1 field - and

                   

                  SUM({$<GoalYear={$(=SaleDateY)}, GoalMonth={$(=SaleDateM)}>}GoalQuantity)

                  doesn't work...

                   

                  Thanks



                    • Set Expression Help
                      Korin Geri

                      Anybody?

                      • Set Expression Help
                        Miguel Angel Baeyens de Arce

                        Hello,

                        Syntax seems to be all right, provided SaleDateY contains a year (so it does according to your previous post) and SaleDateM contains a month in the same way (text, number) it is stores in GoalMonth field. If not, set analysis would return unexpected values... What do you have in SaleDateM variable and how do you soter you months in GoalMonth field?

                          • Set Expression Help
                            Korin Geri

                            I have month number (1..12) - I load it from excel

                            The strange thing is that if I use any of the expressions separetely, it works (for one field) - but not combined.

                             

                            I'm quite sure this is a pretty typical scenario when there is no association by all fields and such calculation needs to be made.. like Budget vs Actual by multiple dimensions..

                            If there are any alternative solutions for this scenario Ill be glad to hear..

                          • Set Expression Help

                            I have been using set analysis right from the start, the $ sign expansion seems to work slightly differently since the introduction of indirect set analysis, for example

                            $(=max(SaleDateY)) should return 2010 however I have had to get round this referencing a variable so that a variable brings back a single value

                            $(vSaleDateY) and do the calc in the variable

                            Please see worked example below

                            count({$< Bkg_Date = {"<=$(vDateYr-3)"} >}Customers)

                             

                            Variable

                            vDateYr-3 =max(Cal.Year)-3

                            hope this is of help

                            Regards,

                            Neil



                             

                            • Set Expression Help

                              SUM({$<GoalYear={$(=SaleDateY)}, GoalMonth={$(=SaleDateM)}>}GoalQuantity)

                              The problem is that SalesDateM has 7 possible values and you are trying to force a selection without the user having made a selection. SaleDateY is going to have the same problem once more than 1 year becomes available. Are you wanting the formula to work accross all possible values? In other words, should GoalMonth select all values in SaleDateM (2, 3, 4, 5, 6, and 7) until a selection is made for one or more months? If so, you have to provide a mechanism for the set expression to see all possible field value through a concatenation.

                              SUM({$<GoalYear={$(=concat(chr(39) & SaleDateY & chr(39), ','))}, GoalMonth={$(=concat(chr(39) & SaleDateM & chr(39), ','))}>} GoalQuantity)

                              In this formula, GoalYear and GoalMonth are handed all possible values of SaleDateY and SaleDateM in a comma separated list. Now no matter what is selected in SateDateY and SaleDateM (single OR multiple selections), you will have the formula calculating for all possible values.