1 Reply Latest reply: Jul 12, 2013 7:42 PM by Stefan Wühl RSS

    Sum is taking all the selections and not the particular ones

      Hello,

       

      In a Straight Table, i want to show the market shares of my products.

       

      The problem is my expression:

      =

      sum({<Date ={">=$(=DateStart) <=$(=DateEnd)"},Month=,Year=>} Value)

      /

      sum({<product={'TOTAL'},Date ={">=$(=DateStart)<=$(=DateEnd)"},Month=,Year=>} Value)

       

      by separately they work excellent they show me, the amounts in another chart. But in this chart that i am making with the Market Shares, having some trouble.

       

      Why am I using product={'TOTAL'}? Because i receive tables of data which i use to make this qv project, and TOTAL is the total amount that also appears just as every product in the table. that's the reason i don't use =sum(TOTAL Value) or something really similar to that.

       

      the big detail is that the first part of the expression: sum({<Date ={">=$(=DateStart) <=$(=DateEnd)"},Month=,Year=>} Value) is making the sum, but in the chart it just shows as a total percentage of the selection. And if i select more than one product it it shows as well just the percentage in the market of all those products, and by each row there just appears a ' - '.

       

      Help anyone. And don't know what else to do, any way to make an expression that works?

      or another solution, like can i use the other chart values to make my market share value percentages?

       

      Thanks

        • Re: Sum is taking all the selections and not the particular ones
          Stefan Wühl

          Your (only) dimension in the chart is 'product', right?

           

          Then sum({<product={'TOTAL'}>} Value) will return zero for all products not equal to 'TOTAL', unless you add the TOTAL qualifier:

           

          =

          sum({<Date ={">=$(=DateStart) <=$(=DateEnd)"},Month=,Year=>} Value)

          /

          sum({<product={'TOTAL'},Date ={">=$(=DateStart)<=$(=DateEnd)"},Month=,Year=>} TOTAL Value)

           

          You can also try something like

           

          =

          sum({<Date ={">=$(=DateStart) <=$(=DateEnd)"},Month=,Year=>} Value)

          /

          sum({<Date ={">=$(=DateStart)<=$(=DateEnd)"},Month=,Year=>} TOTAL Value)

           

          to retrieve the market share of the product, but here the total is calculated using the products shown in the chart (products selected), while in the first expression, you are using the 'TOTAL' product value (independent from your product selection).

           

          Hope this helps,

          Stefan