Announcements
cancel
Showing results for
Did you mean:
Contributor III

## Creating a value to show the % participation

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

1 Solution

Accepted Solutions
MVP

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

this works for me.

Stefan

19 Replies
MVP

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

Contributor III
Author

Hi Stefan,

I tryied using

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

But I got the same results.

Thanks,

Camilo

MVP

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

MVP

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

this works for me.

Stefan

Contributor III
Author

Great Swuehl, it worked perfectly!!!

Why adding total did the job??

MVP

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

Contributor III
Author

Thanks swuehl, now I understand how this works.

Camilo

Contributor III
Author

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

MVP

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>

Community Browser