Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Percentage compared to Total on each row

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

1 Solution

Accepted Solutions

Re: Percentage compared to Total on each row

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)))

10 Replies

Re: Percentage compared to Total on each row

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

Not applicable

Re: Percentage compared to Total on each row

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

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

Regards,
Prashant Sangle
Not applicable

Re: Percentage compared to Total on each row

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

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

Regards,
Prashant Sangle

Re: Percentage compared to Total on each row

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

Not applicable

Re: Percentage compared to Total on each row

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

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)))

Not applicable

Re: Percentage compared to Total on each row

Great!!! That works! Thanks so much!

Community Browser