topic Re: Calculated percentage in Bar Chart in QlikView

Calculated percentage in Bar Chart

Wed, 25 Nov 2020 16:16:04 GMT

The chart below represents a total count for each of the segmented fields (Green, Yellow, Red). Each of the segments is a calculated dimension. Can someone please help me turn this data into percentages? The Y axis should be 0% - 100% and each segment of each bar should be a percentage thereof. Also, how do you get the numbers to appear in each segment rather than below, on the X axis?

Can someone please help?

Thank you!!

Re: Calculated percentage in Bar Chart

Gysbert_Wassenaar — Thu, 10 Mar 2016 18:10:11 GMT

Change your expressions so that they are divided by the sum of all expressions:

Count({<ActualNumeric={">0"}>} If(ActualNumeric>SpLimitLowValue and ActualNumeric<SpLimitUpperValue, ActualNumeric))

/rangesum(

Count({<ActualNumeric={">0"}>} If(ActualNumeric>SpLimitLowValue and ActualNumeric<SpLimitUpperValue, ActualNumeric)),

Count({<ActualNumeric={">0"}>} If(ActualNumeric>Lower_value and ActualNumeric<Upper_value, ActualNumeric)),

Count({<ActualNumeric={">0"}>} If(not (ActualNumeric>SpLimitLowValue and ActualNumeric<SpLimitUpperValue) or

(ActualNumeric>Lower_value and ActualNumeric<Upper_value), ActualNumeric))

)

Change the Number format to Fixed to with as many decimals as you want and enable the Show as Percentage option. Disable the Text on Axis option, enable the Values on Data Points option and on the Presentation tab enable the Plot Values in Segments option.

Re: Calculated percentage in Bar Chart

sunny_talwar — Thu, 10 Mar 2016 18:10:17 GMT

Like this?

Re: Calculated percentage in Bar Chart

Anonymous — Thu, 10 Mar 2016 18:10:32 GMT

HI Lee:

I think you should calculate the proportion representing each expression with respect to the total.

Look at the attachment, to see if it is what you want.

Re: Calculated percentage in Bar Chart

rubenmarin — Thu, 10 Mar 2016 18:23:27 GMT

Hi Lee, looking the data I think the expressions should use different fields, I have changed it, please take a look:

Re: Calculated percentage in Bar Chart

Thu, 10 Mar 2016 21:27:36 GMT

Wow that's perfect! Thank you

Re: Calculated percentage in Bar Chart

Thu, 10 Mar 2016 21:36:56 GMT

Thank you everyone!!!

Re: Calculated percentage in Bar Chart

Thu, 10 Mar 2016 22:45:37 GMT

Maria,

How did you get the data points to display on each segment?

Re: Calculated percentage in Bar Chart

Thu, 10 Mar 2016 22:50:10 GMT

Thanks for your help, but using what you gave me yesterday, I got it right.

Re: Calculated percentage in Bar Chart

Anonymous — Fri, 11 Mar 2016 08:15:31 GMT

In Properties->Presentation, check the option 'Plot Values Inside Segments'.

Re: Calculated percentage in Bar Chart

Fri, 11 Mar 2016 15:25:37 GMT

Thanks! I thought so, but it wouldn't work for one of the graphs, and then mysteriously it did.

Re: Calculated percentage in Bar Chart

Sat, 12 Mar 2016 17:11:19 GMT

One more question...

For the red bar, I only want values returned if "SpLimitLowValue" is > 0. But I can't seem to add a Count If statement correctly.

This is the expression for the red bar:

Count({<ActualNumeric={">0"}>} If(ActualNumeric<SpLimitLowValue, ActualNumeric) or If(ActualNumeric>SpLimitUpperValue, ActualNumeric))
/
(Count({<ActualNumeric={">0"}>} If(ActualNumeric>SpLimitLowValue and ActualNumeric<SpLimitUpperValue, ActualNumeric))+
Count({<ActualNumeric={">0"}>} If(ActualNumeric<SpLimitLowValue, ActualNumeric) or If(ActualNumeric>SpLimitUpperValue, ActualNumeric)))

Thank you!