5 Replies Latest reply: Aug 27, 2010 8:42 PM by John Witherspoon RSS

    Set Analysis

      I have been struggling with this for ever - I am relatively new to Qlikview

      I have Columns Actvalue, ValueTypeID, RunTypeID, DEVEbyS, ScenarioID

       

      I have an expression as follows: sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}>} [DEVEbyS] )

      This gives me values by Scenario - so far so good

       

      I have a second expression: sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}> * <ScenarioID = {"0"}>} [ActValue] )

      This gives me a single value for scenario 0 = also so far so good

       

      here's the problem when I divide expression 1 by expression 2 - I get no data !!

       

      i.e. sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}>} [DEVEbyS] ) / sum ( {<ValueTypeID = {"EVE"}> * <RunTypeID = {"Base"}> * <ScenarioID = {"0"}>} [ActValue] )

       

      Is the set ScenarioID = 0 being applied in tboth expressions when I divide one by the other? or have i messed up something else

        • Set Analysis
          Clever Anjos

          Could you share some data examples?

            • Set Analysis

              Here are some data values

              e.g scenario 400 devebys = (-30346-3758) = -34104

              Bas, EVE, Scenario 0 Actvalue = 188806+82605 = 271411

              Answer wanted = -34104/271411 = -12.57%

               

              Thanks

               

               

              RunTypeIDValueTypeIDScenarioIDActValueDEVEbyS
              BaseEVE400$ 158,460$ (30,346)
              BaseEVE300$ 165,244$ (23,562)
              BaseEVE200$ 172,716$ (16,090)
              BaseEVE100$ 180,771$ (8,035)
              BaseEVE0$ 188,806$ -
              BaseEVE-100$ 196,387$ 7,581
              BaseEVE-200$ 203,685$ 14,879
              BaseEVE-300$ 209,012$ 20,206
              BaseEVE-400$ 212,197$ 23,391
              BaseEVE400$ 78,848$ (3,758)
              BaseEVE300$ 79,716$ (2,890)
              BaseEVE200$ 80,620$ (1,985)
              BaseEVE100$ 81,563$ (1,042)
              BaseEVE0$ 82,605$ -
              BaseEVE-100$ 83,529$ 924
              BaseEVE-200$ 83,915$ 1,309
              BaseEVE-300$ 83,956$ 1,350
              BaseEVE-400$ 83,763$ 1,158
                • Set Analysis
                  Neil Miller

                  First, you don't need to use * between each Set Modifier:

                  sum ( {<ValueTypeID = {"EVE"},RunTypeID = {"Base"},ScenarioID = {"0"}>} [ActValue] )


                  I think you just need to use TOTAL <FIELD> in your Sums to group by the appropriate fields. Here's an expression that gets -12.57 for the Scenario 400s:

                  sum ( {<ValueTypeID = {"EVE"},
                  RunTypeID = {"Base"}>} TOTAL <RunTypeID,ValueTypeID,ScenarioID> [DEVEbyS] )
                  /
                  sum ({<ValueTypeID = {"EVE"},
                  RunTypeID = {"Base"},
                  ScenarioID = {"0"}>} TOTAL <RunTypeID,ValueTypeID,ScenarioID> [ActValue])


                  EDIT: Could probably use:

                  sum ( {<ValueTypeID = {"EVE"},
                  RunTypeID = {"Base"}>} TOTAL <ScenarioID> [DEVEbyS] )
                  /
                  sum ({<ValueTypeID = {"EVE"},
                  RunTypeID = {"Base"},
                  ScenarioID = {"0"}>} TOTAL [ActValue])


                    • Set Analysis

                      Thanks for the reply

                       

                      Firstly

                      I am using Version 9.00.7502.8 SR%

                      and the comma between set modifiers doesn't work - * seems to work as AND (Union)

                       

                      Secondly - the TOTAL field solves half the problem -

                      i.e for the total group

                      Sum of Actvalue for Assets 800,000 plus Liabilities (750,000) = 50000 - NOTE: 50000 is what i want

                       

                      but when I drill down to assets only - I get 800,000 - 0 = 800,000 - I still want the original 50000?

                      does this make sense?

                        • Set Analysis
                          John Witherspoon

                           


                          ajlwhite wrote:I am using Version 9.00.7502.8 SR%
                          and the comma between set modifiers doesn't work


                           

                          Then you probably have a typo.

                           


                          ajlwhite wrote:* seems to work as AND (Union)


                          Intersection, not union. Yes, it works, but learning the right syntax would be much more efficient, both from a coding and maintenance standpoint and from the standpoint of what QlikView has to go through to calculate your result.

                          If you can't get multiple set modifiers to work, how about posting the expression that isn't working?