2 Replies Latest reply: Jul 11, 2013 5:13 PM by ignacionimo RSS

    Not having values for each field

      Hi, i had advanced a little in this problem, i am using a straight table where I want to display the Market Share percentages of each client from the total market.


      I have this expression:


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


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


      i have a list box where i select 1 or more clients, and i have included in that list box the client 'TOTAL' where is the total amount of the clients.  therefore the equation will be something like harold's sales / total sales = Market Share of Harold.


      I have tried many things, I even make text objects for each part of the equation.


      This part in the text object:


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

      Gave me what i wanted i amount of the total sales, if i made any selections of any clients it gave me the amount i wanted.


      But this part:

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

      will give me the sum of the selected cllients, that is good in the text box.


      The problem is that in the straight chart, at this moment shows: if one client is selected, at the end of the row where the Market Share should be is an ' - ' and at the superior row there's the value.

      (Because i checked show expression total).


      When i select more than one client, so i can see which client has bigger market share, is the same in every row at the last colum where the Market Share should be there's an ' - ', and at the top row of every selection is the Market Share of all the products selected over the TOTAL (Because i checked show expression total).


      Please help, i am struggling here.


      Here is what this expressions get me:


      ProductoFeb 2013


      Here is what i wish i could have


      ProductoFeb 2013
        • Re: Not having values for each field
          Stefan Wühl

          Is there a reason why you created a special client 'TOTAL', i.e. calculating the TOTAL in the script, having a static total value?


          If not, I would suggest to use the total qualifier in the sum() aggregation function instead:


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


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



          Ah, I see, your total values is the total market, and your clients don't add up to the total value, i.e. you are looking at a subset of clients.


          But is there a reason to handle the total market share as a client?



          This should work:

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

            • Re: Not having values for each field

              Yes i would use that if that was the case, but, the data comes from a file that already has it. And there are also clients named like:


              Family Smith -----2500

              John Smith   -----1500

              Anna Smith  -----1000


              So in the case i didn´t have the client = 'Total' i will still have those clients = ' Family * '