Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Calculate Dimension Total as Percentage of Grand Total

I'm very much a Qlikview newbie, trying to display a chart showing the relationship between two values, lets call them Sales Volume vs Sales Value.

I have a list of 6 widgets

WidgetVolumeValue
Widget A45000
Widget B67000
Widget C36000
Widget D52000
Widget E1012000
Widget F814000

I want the user to select a widget, and my chart to display a bar for Volume and Value.  However since Volume and Value are on massively different scales, they won't make much sense beside each other

What I want to show instead is volume a as a percentage of total and value as a percentage of the total value.  So when the user selects Widget A, the Volume chart will show 4/36 =~ 0.11.  For Volume it would show 5000/46000 =~ 0.11 (that was an accident, but you get my point).  The implication (I think) being that Widget A's selling price per unit is roughly the same as the average across all units.

Obviously Sum etc. functions will only sum values for the selected Widget, something that will let me take the value for the selected widget and divide it by the total for all widgets.

I get the impression Qlikview wasn't really intended to work with data that's summarised already in this way, but Is this possible?

Thanks

1 Solution

Accepted Solutions
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Hi,

Read the F1 help for Set Analysis and you'll see how much is possible!!  For your specific solution, try:

=Sum(Volume)/Sum({1} Volume)

or

=Sum(Volume)/Sum({<Widget=>} Volume)

The first of these will divide the Sum(Volume) for all the current selections by the TOTAL Sum(Volume) when ignoring ALL selections, the second will only ignore the Widget selection.

Hope this helps,

Jason

View solution in original post

5 Replies
Jason_Michaelides
Luminary Alumni
Luminary Alumni

Hi,

Read the F1 help for Set Analysis and you'll see how much is possible!!  For your specific solution, try:

=Sum(Volume)/Sum({1} Volume)

or

=Sum(Volume)/Sum({<Widget=>} Volume)

The first of these will divide the Sum(Volume) for all the current selections by the TOTAL Sum(Volume) when ignoring ALL selections, the second will only ignore the Widget selection.

Hope this helps,

Jason

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     You can do that.

     Below expression won't consider selection(divide by all the widgets) to divide

     Sum(Volume)/Sum({1} Volume)

     Sum(Value)/Sum({1} Value)

     Below is to get percentage in the selection

     Sum(Volume)/Sum(TOTAL Volume)

     Sum(Value)/Sum(TOTAL Value)

Multiply with 100 which gives you percentage or Check the Show in percentage option in the number tab

Celambarasan

Not applicable
Author

Thanks Jason and Celambarasan  - that's exactly what I was looking for.  Many thanks for the quick response.

Not applicable
Author

I tried all of these, but it only shows the right ratio in the total section of my table. In front of each row it shows 100%

What can I do?

Bonnie
Contributor
Contributor

(RangeSum(Above(TOTAL count({<M_STATUS=p(M_STATUS)>}DISTINCT [PM Number]),0,RowNo(TOTAL)))/COUNT({<M_STATUS=p(M_STATUS)>} TOTAL DISTINCT [PM Number])) * 100

where Numerator is a running total:
(RangeSum(Above(TOTAL count({<M_STATUS=p(M_STATUS)>}DISTINCT [PM Number]),0,RowNo(TOTAL)))

and denominator is total:
COUNT({<M_STATUS=p(M_STATUS)>} TOTAL DISTINCT [PM Number]))

I needed TOTAL of DISTINCT counts in my case, you need TOTAL of Sum

 

Good short review of cumulative sums

https://www.youtube.com/watch?v=AlT_jAMsiGU