topic Percent for stacked value bar in QlikView

Percent for stacked value bar

Wed, 02 Feb 2011 07:53:43 GMT

Hi,

I am having trouble with what I think should be fairly simple so I assume the answer is staring me in the face and I just keep looking past it.

I want to have a graph that represents the SUM of a value with different calculation criteria for each expression, but instead of the actual value being displayed on the data point I want the percentage of that bar. Here is an example of what a table like this looks like:

I try to do this in QV, there is no option that I can find to just display the percentage of the bar that an expression takes up that I can find, so I thought I could just make two invisible expressions to calculate the percentages and lay the data over top. This is what happens when I try this:

It displays only the first value on both stacks, and when you scroll over each stack it displays both percentages. I thought maybe it assigns each percentage expression to each bar so I tried to us an IF statement to create two different outcomes in one percentage expression but that just resulted in null percentage (since there are two values where there should be one).

I have attached this example QVW to show what I did. Does anyone know how to achieve this and what I am missing here? Thank you.

Percent for stacked value bar

jonathandienst — Wed, 02 Feb 2011 09:18:35 GMT

What is happening is that both values are being displayed, but the white meat % overalys the red meat %. I can suggest two workarounds:

Have the % display using Text on Axis - this keeps them separate
Replace the two % expressions with a single expression that produces text, which you can display on the data points or on the axis. Something like:

= 'Red: ' & Num(SUM(IF(Product = 'Beef' OR Product = 'Lamb', Sales)) / SUM(Sales), '#0%') & '
White: ' & Num(SUM(IF(Product = 'Fish' OR Product = 'Chicken' OR Product = 'Pork', Sales)) / SUM(Sales), '#0%')

(Note the embedded line break is significant)

Hope that helps

Jonathan

Percent for stacked value bar

kji — Wed, 02 Feb 2011 09:29:23 GMT

Instead of creating a new expression you could put the percent calculation into the first expression using the Dual function.

Percent for stacked value bar

Tue, 08 Feb 2011 09:40:56 GMT

Thanks for both of your help I used a solution based on both that is satisfactory for my needs.

For the main value expressions I used DUAL as such:

DUAL(NUM(SUM(IF(Product = 'Beef' OR Product = 'Lamb', Sales)) / SUM(Sales), '#0.0%'), SUM(IF(Product = 'Beef' OR Product = 'Lamb', Sales)))

That allowed me to use "Values on Data Points" to display the percentage over the stacks and keep the stacks and axis based on the normal values.

Then I used a third dimension with the expression:

'Red Meat: ' & NUM(SUM(IF(Product = 'Beef' OR Product = 'Lamb', Sales)), '#0,000') & '
White Meat: ' & NUM(SUM(IF(Product = 'Fish' OR Product = 'Chicken' OR Product = 'Pork', Sales)), '#0,000')

That allowed me to tick "Text as Pop-up" and still display the actual values when moving the cursor over the stacks. It displays both actual values on both stacks. It would be good to somehow exclude one out but this solution is close enough I think.