Creating a value to show the % participation

[camilo]

Hi, I would like to create a field that shows every line of my balance as a sales percentage, for example:

Account    Real  % of Sales

Sales        200         100%

Costs         -40          -20%

Total            60           30%

I´m trying to create an expression "% of Sales" like this:

=sum(Real)/(Sum( {$<[Account]={Sales}>} Real))

But I just get this:

Account    Real  % of Sales

Sales        200         100%

Costs         -40                   

Total            60           30%

In the DB, both Sales and Costs are in the same column (Account).

Thanks for your help!!

[swuehl]

Hi Camilo,

first, I think it should be

=sum(Real)/(Sum( {$<[Account]={'Sales'}>} Real))

(literal within {} because your want to filter for specific value).

Regards,

Stefan

[camilo]

Hi Stefan,

I tryied using

=sum(Real)/(Sum( {$<[Account]={'Sales'}>} Real))

But I got the same results.

Thanks,

Camilo

[swuehl]

Hi Camilo,

it might be helpful if you could post an example qvw.

I was also wondering why you got the above total row. I would expect 160 as value for real.

Regards,

Stefan

[swuehl · Accepted solution]

Ok, please try

=sum(Real)/(Sum( total {$<[Account]={'Sales'}>} Real))

this works for me.

Stefan

[camilo]

Great Swuehl, it worked perfectly!!!

Why adding total did the job??

Many thanks for your help!!!!!

[swuehl]

Well,

sum( [{set_expression}][ distinct ] [ total [<fld {, fld}>]] expression )

sum returns the aggregated sum of expression or field iterated over the chart dimension(s).

So if want to disregard the chart dimensions, we need the total qualifier.

We then may use a set expression to select the values to sum. If we don't disregard the chart dimension, we got an empty value list to sum, because the chart dimension in row (i.e. Costs) and selection set expression (Account=Sales) have no values in common. In other word, you need to override the chart dimension with total qualifier, the set expression won't do that.

Hope this helps,

Stefan

[camilo]

Thanks swuehl, now I understand how this works.

Camilo

[camilo]

Hi,

I have another question related to this topic.

I added month columns to separate the data, but the % of sales participation divide the month values by the total sales ¿is there any way to divide by the corresponding month sales?

I have this table:

Ex1	$$	% of Sales
Sales	110	100%
Costs	80	73%
Total	30	27%

I use moth to separate the data on columns:

	JAN		FEB		TOTAL
EX2	$$	% of Sales	$$	% of Sales	$$	% of Sales
Sales	40	36%	70	64%	110	100%
Costs	15	14%	65	59%	80	73%
Total	25	23%	5	5%	30	27%

I would like to have this kind of table having a 100% on the sales field every month:

	JAN		FEB		TOTAL
EX3	$$	% of Sales	$$	% of Sales	$$	% of Sales
Sales	40	100%	70	100%	110	100%
Costs	15	38%	65	93%	80	73%
Total	25	63%	5	7%	30	27%

Thanks to any one who can help me with this

Camilo

[swuehl]

Hi camilo,

without trying myself, I would have a look at the total qualifier with dimensions added (see above or help file, total<fld,fld>), i.e. replace the total by total<Month>