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

# 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).

Here is what this expressions get me:

 Producto Feb 2013 0,79985 Harold - Frank - James - Pete -

Here is what i wish i could have

 Producto Feb 2013 0,79985 Harold 0,251 Frank 0,220 James 0,170 Pete 0,155
• ###### Re: Not having values for each field

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?

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