Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

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

View solution in original post

10 Replies
sunny_talwar

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

Not applicable
Author

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

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

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!

PrashantSangle

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

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
sunny_talwar

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

Not applicable
Author

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

sunny_talwar

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
Author

Great!!! That works! Thanks so much!