Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate % of Total Expression

I have a straight table, with Customer dimension and Sum(Sales) as expression.

I want another Expression that should show me that Customer sales as a % of Total Sales. I am unable to figure out how to do that. Any help is appreciated.

CustomerSales% Sales
A1010%
B2020%
C3030%
D4040%
Total100

Thanks,
Sagar

10 Replies
abeyphilip
Creator II
Creator II

Hi Sagar,

For '%' expression:

sum (sales) / sum (total sales)

Then in number tab, show as %.

Regards,

Abey

Not applicable
Author

Abey,

I tried Sum( total Sales) but that doesnt give right answer as the total takes all the values in the database and not only the ones I have selected in my table. For example, my database looks like this:

CustomerSales
A-10
B-20
C-30
D-40
E10
F20
Total Sales-70

In my dimension, I want to show only the negative sales.

CustomerSales
A-10
B-20
C-30
D-40
Total -ve Sales-100

Then I want to show % for each of the rows against -ve sales.

Sum(Total Sales) is giving me -70, while I want the total for only the rows selected in my dimension. I hope I am able to ask the question correctly.

kangaroomac
Partner
Partner

Hi Sagar,

To create the % column, do as Abey said above. If you want to see the relative % (i.e. of Total Sales, Customer A Contributes x %, etc.), select the "Relative" checkbox in your expressions tab of your straight table properties.

Hope this helps.

Not applicable
Author

Thanks Abey, Ian. Selecting the 'Relative' checkbox solved my problem.

My next task if to have a slider with these relative % values. Is that even possible?

kangaroomac
Partner
Partner

Hi Sagar,

I'm not too sure, I've never attempted something similar. I would suspect it might be a bit easier if you created the field in the load script, and then used that field in your slider.

Hope this steers you in the right direction.

Not applicable
Author

Ian,

I attempted something on these line. I created a variable and then created a slider that changes this variable. The range of the variable is 0% to 100%.

Now, I want to show the above mentioned table but only those dimensions that are selected in the slider:

CustomerSales% Sales
A-1010%
B-2020%
C-3030%
D-4040%

So, if the slider is selected at 40%, I want to filter the dimension to show only Customer D.

kangaroomac
Partner
Partner

Hi Sagar,

One way of doing it, and I'm not sure if this is what you're after, is having an IF statement in the Expression of the Sales Column, i.e.:

Sum(IF((Sum(Sales) / Sum(TOTAL Sales)) >= $(yourVariable), Sales)

This would give you the following scenario: If a user selects 40% for the slider, D will show as requested. If a user selects 20%, B, C and D should be shown.

Not applicable
Author

Hi Ian,

The problem with Sum(Total Sales) is that it does a total on all the rows present in the database, and not only the ones that I have selected in the dimension. So, for example, if the Customers shown above are only from 1 region (i have specified an IF clause in dimension), the Sum(Total Sales) will show all sales from all regions. Is there a way to get Total Sales only for the Customers present in the dimension? If yes, then I can implement your above recommendation.

Not applicable
Author

Hi all,

I am attaching a test file, where I have tried to implement Sum(Total Sales). As you can see, it is giving incorrect value. Please suggest a way so that I can use my slider to show only the relevant values in my straight table.