topic Re: Ratio in a bar chart in QlikView

Ratio in a bar chart

Wed, 25 Nov 2020 16:16:04 GMT

Hi guys

I'm still a rookie and need some simple help.

How can I show the ratio within a field in a bar chart every month?

Fx. for one month I have a field "PRODUCT" within the field i have "Product 1" "Product 2" etc. If I chose "Product 1" I can see that it has been sold 10 times(frequency), for "Product 2" 30 times(frequency) etc.

I want to make a bar that show the ratio between "Product 1" and the others. If we only had the two products for one month the ratio would be 10/30 = 33.3 %.

Please let me know if you need me to clarify anything

Thanks!

Re: Ratio in a bar chart

Jason_Michaelides — Tue, 06 Sep 2016 16:33:21 GMT

Sum(Frequency)/(Sum(TOTAL Frequency)-Sum(Frequency))

Hope this helps,

Jason

Re: Ratio in a bar chart

sunny_talwar — Tue, 06 Sep 2016 23:40:45 GMT

May be you need this:

Dimension:

MonthYearField

PRODUCT

Expression:

Count(PRODUCT)/(Count(TOTAL <MonthYearField> PRODUCT) - Count(PRODUCT))

Re: Ratio in a bar chart

Wed, 07 Sep 2016 07:13:53 GMT

Hmm it doesn't work.

I have chosen Month and Product as dimension and as expression the text you have wrote.

The values are wrong and I get a bar chart with all the products I have, I only need to show Product 1 / Product(all products) and then on a monthly basis.

Re: Ratio in a bar chart

Jason_Michaelides — Wed, 07 Sep 2016 16:02:13 GMT

Sum(Frequency)/(Sum(TOTAL <Month> Frequency)-Sum(Frequency)) should work. Can you share your app please?

Re: Ratio in a bar chart

sunny_talwar — Wed, 07 Sep 2016 16:52:14 GMT

Did you replace MonthYearField with Month? MonthYearField was a placeholder and needed to be replaced with the field you were using in your chart.

Like jason.michaelides‌ mentioned, try this

Sum(Frequency)/(Sum(TOTAL <Month> Frequency)-Sum(Frequency))

Re: Ratio in a bar chart

MarcoWedel — Thu, 08 Sep 2016 00:33:53 GMT

Hi,

one example might be:

or to avoid "Errors in expression" enumerating all fields of the dimension group in the total qualifier (instead of the group name itself):

One note: If you compared the sales of one product to the sales of all products (including itself), the usage of stacked bars might be an option:

hope this helps

regards

Marco

Re: Ratio in a bar chart

Fri, 09 Sep 2016 07:54:31 GMT

It worked! Thanks!

Re: Ratio in a bar chart

Fri, 09 Sep 2016 07:56:00 GMT

It's working - thanks!