Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP

Creating a value to show the % participation

Ok, please try

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

this works for me.

Stefan

View solution in original post

19 Replies
Highlighted
MVP
MVP

Creating a value to show the % participation

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

Highlighted
Contributor III
Contributor III

Creating a value to show the % participation

Hi Stefan,

I tryied using

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

But I got the same results.

Thanks,

Camilo

Highlighted
MVP
MVP

Creating a value to show the % participation

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

Highlighted
MVP
MVP

Creating a value to show the % participation

Ok, please try

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

this works for me.

Stefan

View solution in original post

Highlighted
Contributor III
Contributor III

Creating a value to show the % participation

Great Swuehl, it worked perfectly!!!

Why adding total did the job??

Many thanks for your help!!!!!

Highlighted
MVP
MVP

Creating a value to show the % participation

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

Highlighted
Contributor III
Contributor III

Creating a value to show the % participation

Thanks swuehl, now I understand how this works.

Camilo

Highlighted
Contributor III
Contributor III

Creating a value to show the % participation

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

Highlighted
MVP
MVP

Creating a value to show the % participation

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>