12 Replies Latest reply: Dec 13, 2016 11:07 AM by Vineeth Pujari RSS

    Set analysis with variables

    John Griggs

      Hi,

       

      I have a couple of variables, each having the same sort of structure - which is summing stuff together. e.g.

       

      vMarketableWeight = (SUM[field A] + SUM[field B] + SUM[field C] etc....)

       

      These work fine when called from within a visualization with $(vMarketableWeight).

       

      I've hit a snag using these with set analysis. The syntax I want to use is:

       

      SUM({$<

          MarketableOrOutsize = "",

          [Size Bucket] = ""

          >}

          $(vMarketableWeight)/($(vMarketableWeight) + $(vOutsizeWeight))* 100

          )

       

      This bit: $(vMarketableWeight)/($(vMarketableWeight) + $(vOutsizeWeight))* 100 works fine on its own.

       

      I'm guessing it's something to do with sums of sums - or evaluation precedence....but my knowledge is limited.

       

      Any ideas please?

       

      I don't want to change the structure of my variables as they're well used.

       

      Cheers,

        • Re: Set analysis with variables
          Bill Markham

          Have you got missing bracket, maybe try something like this :

           

          SUM({$<

              MarketableOrOutsize = "",

              [Size Bucket] = ""

              >}

            ($(vMarketableWeight)/($(vMarketableWeight) + $(vOutsizeWeight))* 100

              )

           

           

          Also when you do $(vMarketableWeight)/($(vMarketableWeight) you are dividing $(vMarketableWeight) by itself and that will always return one - maybe a typo.

            • Re: Set analysis with variables
              John Griggs

              Hi Cheers for the reply,

               

              The calc works fine on its own. It's only when there's set analysis it doesn't work...which is due to the sum or sum problem.

               

              Brackets are OK (I've tested without the set analysis) - and the internal brackets means it doesn't divide by itself as it forces the addition first (the underlined bit) - it probably seems backward the way it's laid out, but it's necessary as the outsize and marketable weights are built up of many size bands...all of which are valid in their own right (so have to be sums when the user makes multiple record selections), but all off which only ever belong to one set - Marketable or Outsize.

               

              I'm 99% sure it's the SUM at the beginning of the set analysis that's the problem....just don't know how to fix it.

               

              $(vMarketableWeight)/($(vMarketableWeight) + $(vOutsizeWeight))* 100

               

               

               

            • Re: Set analysis with variables
              Gysbert Wassenaar

              The expression in your variable contains aggregations: SUM(....). You then put an aggregation function into another aggregation: SUM(SUM(...)). That's nonsense unless you specify over which dimensions the inner sums should be aggregated. You need to use the aggr function to do that: SUM( AGGR( SUM(....) , DimA, DimB, ..., DimX)). See this document for more information: QlikView Technical Brief - AGGR.docx

              • Re: Set analysis with variables
                Vineeth Pujari

                I think your Expression should be as below,the entire thing

                 

                SUM({$<

                    MarketableOrOutsize = "",

                    [Size Bucket] = ""

                    >}

                    $(vMarketableWeight) )

                 

                /

                 

                (

                SUM({$<

                    MarketableOrOutsize = "",

                    [Size Bucket] = ""

                    >} $(vMarketableWeight) )

                +

                 

                SUM({$<

                    MarketableOrOutsize = "",

                    [Size Bucket] = ""

                    >} $(vOutsizeWeight) )

                 

                )

                  • Re: Set analysis with variables
                    John Griggs

                    Cheers Vineeth. I tried that one too - but no luck! :-(

                     

                    I know it's the SUM or SUMS that's the problem as my variables already contain summing.....so am guessing that's why this doesn't work either.

                     

                    Cheers,

                    John

                      • Re: Set analysis with variables
                        Vineeth Pujari

                        Can you post the exact expression you used? want to make sure we are not  missing curly brackets

                         

                        SUM({$<

                            MarketableOrOutsize = {'VALUE'},

                            [Size Bucket] ={''}

                            >}

                            $(vMarketableWeight) )

                         

                        /

                         

                        (

                        SUM({$<

                          MarketableOrOutsize = {'VALUE'},

                            [Size Bucket] ={''}

                            >}

                         

                            >} $(vMarketableWeight) )

                        +

                         

                        SUM({$<

                          MarketableOrOutsize = {'VALUE'},

                            [Size Bucket] ={''}

                            >}

                         

                            >} $(vOutsizeWeight) )

                         

                        )

                    • Re: Set analysis with variables
                      John Griggs

                      Hi Vineeth,

                       

                      I misled a bit (sorry) I was trying to empty the two set modifiers (and forgot the syntax! - no swirly brackets or quotes required!!)

                       

                      It still doesn't work - but this is effectively what I'm trying to do (but with the parts separated as you've suggested):

                       

                      SUM({$<

                          MarketableOrOutsize =,

                          [Size Bucket] =

                          >}

                          $(vMarketableWeight)  )

                       

                      /

                       

                      (

                      SUM({$<

                        MarketableOrOutsize =,

                          [Size Bucket] =

                          >}

                       

                          >} $(vMarketableWeight) )

                      +

                       

                      SUM({$<

                        MarketableOrOutsize =,

                          [Size Bucket]=

                          >}

                       

                          >} $(vOutsizeWeight) )

                       

                      )

                       

                       

                      Still pretty sure its the whole SUM or SUM thing that's the problem (i.e. each of the three variables are pretty much the same - i.e. SUM[field a] + SUM[field b] + SUM[field c] etc..

                      It's done like this because we're able to choose these fields in filter visualisations.

                       

                      Cheers,