Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
the_murkle_man
New Contributor II

Percentage of SubTotal

Hi Guys,

I have recently been stumped trying to figure out how to create a % of a sub total.

For example I have a pivot table showing something like this:

County     City     Count of Sales

UK           London          10

UK           Liverpool        15

UK           Manchester    75

France     Paris              90

France     Nice               10         

So for the UK I would like to have another column saying 10% for London, 75% for Manchester etc.

I have been trying to create this in Set Analysis using the Aggr function, however it always uses the total of the entire pivot table, not the sub totals!

Any support would be massively appreciated!

Regards

1 Solution

Accepted Solutions
thornofcrowns
Valued Contributor II

Re: Percentage of SubTotal

Is this what you want?

QV_Pivot_Extra.PNG

11 Replies

Re: Percentage of SubTotal

Have you tried it with a pivot-table and enabled partial-sums? Further you might need a total statement within your expressions, like: count(TOTAL <City> Sales)

- Marcus

the_murkle_man
New Contributor II

Re: Percentage of SubTotal

Yes I have, the number I want to get the percentage of is the partial sum total.

By doing what you have suggested I get 0% across the board...

I envisioned I would need to do something involving an Aggr function, before dividing that by the sales.

MVP
MVP

Re: Percentage of SubTotal

Hi,

Try this expression

count(TOTAL <Country> Sales)

thabang_petje
Contributor III

Re: Percentage of SubTotal

Hi,

You can try, count(<City> Sales)/count(TOTAL <Country> Sales). This is if you have Country as a dimension. Than go to the properties->Number, for the expression, choose to show as a percentage

tahirkhalil
New Contributor III

Re: Percentage of SubTotal

Hi,

Try using

=sum(SalesCount)/Aggr( 

Sum ( TOTAL <County> [Count of Sales] ) 

,City,County)

Probably this is what I understand.

thornofcrowns
Valued Contributor II

Re: Percentage of SubTotal

Is this what you want?

QV_Pivot_Extra.PNG

senpradip007
Valued Contributor III

Re: Percentage of SubTotal

Have a look at this. May this help you.

the_murkle_man
New Contributor II

Re: Percentage of SubTotal

Hi James,

This is exactly what I wanted, thanks a lot!

Jim

thornofcrowns
Valued Contributor II

Re: Percentage of SubTotal

No worries, happy to help.

Community Browser