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
      0,79985
      Harold-
      Frank-
      James-
      Pete-

       

      Here is what i wish i could have

       

      ProductoFeb 2013
      0,79985
      Harold0,251
      Frank0,220
      James0,170
      Pete0,155
        • 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)

           

          [edit]:

          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?

           

          [edit2]:

          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 * '