Not having values for each field

[#1]

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:

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

[swuehl]

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)

[#3]

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