topic Re: Show Percentage? in QlikView

Show Percentage?

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a data of different vendors from different countries with their respective invoice amounts. Is it possible to show the percentage of a respective vendor to the total of its country?

If I select vendor ABC Ltd, I want to see it's share of 10% of total invoice amount in US.

Thanks for the help

Re: Show Percentage?

sunny_talwar — Fri, 09 Dec 2016 13:37:06 GMT

May be like this:

Dimension:

Vendor

Country

Expression:

Sum([Invoice Amount])/Sum(TOTAL <Country> [Invoice Amount])

Re: Show Percentage?

vinieme12 — Fri, 09 Dec 2016 14:23:09 GMT

Sample data with expected o/p please

Re: Show Percentage?

Anonymous — Fri, 09 Dec 2016 15:39:51 GMT

Hi Sunny,

The formula doesn't work when I select a particular vendor, it shows 100%.

What I am looking for is as follows, let's say Australia has 5 vendors

A, B, C, D,E with 10 USD each, so if I select vendor A, the percentage of the vendor in that country should be 20%.

Hope that clarifies

Re: Show Percentage?

sunny_talwar — Fri, 09 Dec 2016 15:41:35 GMT

May be you have to ignore selection in Vendor field in the denominator expression?

Sum([Invoice Amount])/Sum(TOTAL <Country> {<Vendor>} [Invoice Amount])

Re: Show Percentage?

Anonymous — Fri, 09 Dec 2016 15:46:00 GMT

Here you go Vineeth

Re: Show Percentage?

vishsaggi — Fri, 09 Dec 2016 17:10:03 GMT

Did you try using Sunny's expression?

Extending his expression find the attached file and let us know if this is what you are looking for?

Re: Show Percentage?

vinieme12 — Sat, 10 Dec 2016 13:53:48 GMT

Same as Sunny's Expressions

sum([Invoice Amount USD])

sum({<Vendor>} TOTAL <Location> [Invoice Amount USD])

Re: Show Percentage?

Anonymous — Mon, 12 Dec 2016 08:43:18 GMT

Thank you both for the formula, it works fine. Can you suggest a small tweak to it though?

What if I want to look at only some particular vendors in the chart? I am using the following formula in another chart, can we modify your formula based on that? I tried adding another expression but that didn't work

sum({<[Vendor Name] = {"*DELL*","*MICROSOFT*"}>}[Invoice Amount USD])

Re: Show Percentage?

Anonymous — Mon, 12 Dec 2016 08:56:46 GMT

Use single quotes instead...

Re: Show Percentage?

Anonymous — Mon, 12 Dec 2016 12:17:12 GMT

Hi Sunny,

This works, is there a way to show only a few vendors to include in this? E.g. if I just want to see Vendor = Dell and Microsoft only.

Thanks

Re: Show Percentage?

sunny_talwar — Mon, 12 Dec 2016 12:21:08 GMT

Both in the numerator and denominator or or just in the numerator?

May be this:

Sum({<Vendor = {'Dell', 'Microsoft'}>}[Invoice Amount])/Sum(TOTAL <Country> {<Vendor = {'Dell', 'Microsoft'}>} [Invoice Amount])

Sum({<Vendor = {'Dell', 'Microsoft'}>}[Invoice Amount])/Sum(TOTAL <Country> {<Vendor>} [Invoice Amount])

Re: Show Percentage?

Anonymous — Mon, 12 Dec 2016 13:25:21 GMT

Thanks a lot