topic Re: Percentage compared to Total on each row in QlikView

Percentage compared to Total on each row

Wed, 08 Jun 2016 15:39:23 GMT

Hey!

I would like to create a percentage compared to the Total on each row. Currently I can only show what each row is contributing to the overall Total. The picture explains my issue maybe better:

That shows how I would like to have it everywhere. However, if I expand my Business Unit now I can not see the 100% on each row.

I hope my question is more or less clear. Each line should add up to 100%, since the category is either call-off, one time buy or svo.

Thanks in advance!

Jonas

Re: Percentage compared to Total on each row

sunny_talwar — Wed, 08 Jun 2016 15:58:57 GMT

Can you share the expression you are using to calculate %?

Re: Percentage compared to Total on each row

Thu, 09 Jun 2016 06:59:47 GMT

Of course, this is how it looks like:

(sum({<VersionID = {'COM'}, [Budget Version]=,[Purchase Order Flag]={'Purchase Order'}>}Value/$(CurrencyFactor))
+
sum({<VersionID = {'ACT'}, [Budget Version]=, [Purchase Order Flag]={'Purchase Order'}> }Value/$(CurrencyFactor)))
/
(sum (total{<VersionID = {'COM'}, [Budget Version]=,[Purchase Order Flag]={'Purchase Order'}>}Value/$(CurrencyFactor))
+
sum (total{<VersionID = {'ACT'}, [Budget Version]=, [Purchase Order Flag]={'Purchase Order'}> }Value/$(CurrencyFactor)))

Re: Percentage compared to Total on each row

PrashantSangle — Thu, 09 Jun 2016 07:12:11 GMT

Hi,

try

(sum({<VersionID = {'COM'}, [Budget Version]=,[Purchase Order Flag]={'Purchase Order'}>}Value/$(CurrencyFactor))
+
sum({<VersionID = {'ACT'}, [Budget Version]=, [Purchase Order Flag]={'Purchase Order'}> }Value/$(CurrencyFactor)))
/
(sum (total<PO Category> {<VersionID = {'COM'}, [Budget Version]=,[Purchase Order Flag]={'Purchase Order'}>}Value/$(CurrencyFactor))
+
sum (total<PO Category> {<VersionID = {'ACT'}, [Budget Version]=, [Purchase Order Flag]={'Purchase Order'}> }Value/$(CurrencyFactor)))

Regards

Re: Percentage compared to Total on each row

Thu, 09 Jun 2016 07:45:02 GMT

Hi,

it changed the percentages (I applied your coding to the "% Test") and now its adding up to 100% in each column of PO Category:

But I would like to have it 100% on each row. Even than on the lowest level, a single Purchase Order should either bei Category Call-Off, ... ... :

Thanks again for the support!

Re: Percentage compared to Total on each row

PrashantSangle — Thu, 09 Jun 2016 11:51:12 GMT

hi,

did not understand your requirement properly.

but on which ever level you want % add that column in Total mode

Total<PO Category,Cost Center>

Regards

Re: Percentage compared to Total on each row

sunny_talwar — Thu, 09 Jun 2016 12:06:04 GMT

Do you want row wise 100% or column wise 100%? You can only have one of them in one particular expression.

Re: Percentage compared to Total on each row

Thu, 09 Jun 2016 12:54:30 GMT

Hmm.. I would like to see row wise always 100% on each level (Business Unit, Cost Center, Purchase Order) no matter if some of them are expanded, always adding up to 100% in a row..

Re: Percentage compared to Total on each row

sunny_talwar — Thu, 09 Jun 2016 13:38:29 GMT

Try this:

(Sum({<VersionID = {'COM'}, [Budget Version]=,[Purchase Order Flag]={'Purchase Order'}>}Value/$(CurrencyFactor))
+
Sum({<VersionID = {'ACT'}, [Budget Version]=, [Purchase Order Flag]={'Purchase Order'}> }Value/$(CurrencyFactor)))
/
(Sum (TOTAL <[Business Unit], [Cost Center]> {<VersionID = {'COM'}, [Budget Version]=,[Purchase Order Flag]={'Purchase Order'}>}Value/$(CurrencyFactor))
+
Sum (TOTAL <[Business Unit], [Cost Center]> {<VersionID = {'ACT'}, [Budget Version]=, [Purchase Order Flag]={'Purchase Order'}> }Value/$(CurrencyFactor)))

Re: Percentage compared to Total on each row

Thu, 09 Jun 2016 14:33:13 GMT

Great!!! That works! Thanks so much!

Re: Percentage compared to Total on each row

sunny_talwar — Thu, 09 Jun 2016 15:18:28 GMT

Awesome, please close the thread if you got what you wanted.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny