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: 
steveniow
Contributor III
Contributor III

Percentage Calculation wrong in Total Column

Hi,

 

I have a pivot table in which I calculate "count({<CurrentIP={'Y'},PathwayRevIdxChange={1}>}DISTINCT Hospid)"and then a % column "count({<CurrentIP={'Y'},PathwayRevIdxChange={1}>}DISTINCT Hospid)/sum(PWCount)"

The percentage given for each individual column is correct but the Total is incorrect. Not sure why I cannot insert a screen shot. Sorry.

Can anyone explain why and how to correct please?

Kind regards

Steve

 

 

Labels (2)
18 Replies
steveniow
Contributor III
Contributor III
Author

Hi,

thanks for taking the time on this. what sample do you need?

the table I have shared shows the EOL row as having a total % of 2.3%. The formula should be calculated using the #/count(PWCount) which from the table is 4/219. however 4/219 is actually 1.8% not 2.3%.

i checked the individual % in the rest of the row and they are all correct - e.g. 1/16 for ALVE is 6.3% and 1/24 for LUCM is 4.2%.

so why does the total column give the wrong % when all the other values in the row are correct?

let me know what you need and I'll send it.

Kind Regards

Steve

 

sunny_talwar

like a qvw file

steveniow
Contributor III
Contributor III
Author

Creating a qvw to send you I have found the problem.

the denominator in the % calculation is not what I expected. the sum(PWCount) is not giving me the total pathways I expected - i.e. they they should be all 224 in the totals column

so - how do I get the percentage to provide a denominator which is the total in each column

sunny_talwar

May be using TOTAL Qualifier... Sum(TOTAL Measure)

steveniow
Contributor III
Contributor III
Author

thank you - however...

now perfect in the total column but applys the same 224  total to each individual ward also, but these should have their own averages/totals...

sorry... trying to build a simple qvd to demonstrate the issue.

sunny_talwar

I am not sure I follow... I think a sample qvw will help me understand what you are trying to get

steveniow
Contributor III
Contributor III
Author

Hi Sunny,

I have attached a QVW with the same numbers in. I can't send the original as it is too big and contains confidential info but this is doing the same thing.

to be clear. each individual % column is correct. however the Total % column is wrong.

Kind regards

Steve

sunny_talwar

May be this

[#]/sum(TOTAL <WardCode> PWCount)
steveniow
Contributor III
Contributor III
Author

You are a Genius - thank you so much.

Not come across that particular form of expression before but has worked like a dream.

Cheers

Steve