Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
camilo
Contributor III
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).

Thanks for your help!!

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Ok, please try

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

this works for me.

Stefan

View solution in original post

19 Replies
swuehl
MVP
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

camilo
Contributor III
Contributor III
Author

Hi Stefan,

I tryied using

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

But I got the same results.

Thanks,

Camilo

swuehl
MVP
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

swuehl
MVP
MVP

Ok, please try

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

this works for me.

Stefan

camilo
Contributor III
Contributor III
Author

Great Swuehl, it worked perfectly!!!

Why adding total did the job??

Many thanks for your help!!!!!

swuehl
MVP
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

camilo
Contributor III
Contributor III
Author

Thanks swuehl, now I understand how this works.

Camilo

camilo
Contributor III
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
Sales110100%
Costs8073%
Total3027%

I use moth to separate the data on columns:

JANFEBTOTAL
EX2$$% of Sales$$% of Sales$$% of Sales
Sales4036%7064%110100%
Costs1514%6559%8073%
Total2523%55%3027%

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

JANFEBTOTAL
EX3$$% of Sales$$% of Sales$$% of Sales
Sales40100%70100%110100%
Costs1538%6593%8073%
Total2563%57%3027%

Thanks to any one who can help me with this

Camilo

swuehl
MVP
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>