topic Re: Percentage Calculation wrong in Total Column in QlikView

Percentage Calculation wrong in Total Column

steveniow — Sat, 16 Nov 2024 19:40:18 GMT

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

Re: Percentage Calculation wrong in Total Column

sunny_talwar — Thu, 14 Nov 2019 12:41:22 GMT

What are you getting and what do you expect to see? Without knowing this, it would be difficult to give much help... can you may be mockup few rows to show what you get?

Re: Percentage Calculation wrong in Total Column

Brett_Bleess — Tue, 19 Nov 2019 14:54:22 GMT

Steven, I have let the site admins know you were having an issue with posting image etc., they should be getting in touch hopefully, and then maybe that will allow you to answer Sunny such that we can try to get you some help here. I do have one thing though, check out the following, this may be what is wrong with the total now that I think about it:

https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/ChartFunctions/NestedAggregations/sum-of-rows-pivot-tables.htm

Regards,
Brett

Re: Percentage Calculation wrong in Total Column

Sue_Macaluso — Tue, 19 Nov 2019 15:34:24 GMT

@steveniow Hi there, Are you referring to adding a screenshot to this post? If this is the case, you can click on the little camera on the top of the body of this message. I have provided a screenshot below. If you are talking about a screenshot somewhere else can you please elaborate on that. I signed on as you verified that you have the capability, so if it is on this community platform, you might need to check with your IT. Let us know either way. You cannot edit your post after it's been replied to, however for your screenshot just add a comment or reply and you will be able to add. Thank you for participating.

Re: Percentage Calculation wrong in Total Column

steveniow — Fri, 22 Nov 2019 10:35:45 GMT

thank you

Re: Percentage Calculation wrong in Total Column

steveniow — Fri, 22 Nov 2019 10:45:37 GMT

HI Sunny,

looks like this may work?

Just to confirm - 4/219 is 1.8% not the 2.3% showing in he table.

Re: Percentage Calculation wrong in Total Column

steveniow — Fri, 22 Nov 2019 10:56:04 GMT

Thanks Brett, I have tried using the aggr function but to no avail. I changed the formula shown to

avg(aggr(count({<CurrentIP={'Y'},PathwayRevIdxChange={1}>}DISTINCT Hospid)))

kind regards

Steve

Re: Percentage Calculation wrong in Total Column

sunny_talwar — Fri, 22 Nov 2019 11:56:32 GMT

What is your expression for #?

Re: Percentage Calculation wrong in Total Column

steveniow — Fri, 22 Nov 2019 12:05:04 GMT

count({<CurrentIP={'Y'},PathwayRevIdxChange={1}>}DISTINCT Hospid)

Re: Percentage Calculation wrong in Total Column

sunny_talwar — Fri, 22 Nov 2019 12:10:09 GMT

I am not able to understand the issue, may be because I don't see it. Would you be able to share a sample to show the issue?

Re: Percentage Calculation wrong in Total Column

steveniow — Fri, 22 Nov 2019 12:28:20 GMT

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

Re: Percentage Calculation wrong in Total Column

sunny_talwar — Fri, 22 Nov 2019 12:37:58 GMT

like a qvw file

Re: Percentage Calculation wrong in Total Column

steveniow — Mon, 25 Nov 2019 11:29:52 GMT

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

Re: Percentage Calculation wrong in Total Column

sunny_talwar — Mon, 25 Nov 2019 12:31:52 GMT

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

Re: Percentage Calculation wrong in Total Column

steveniow — Mon, 25 Nov 2019 12:35:29 GMT

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.

Re: Percentage Calculation wrong in Total Column

sunny_talwar — Mon, 25 Nov 2019 12:42:02 GMT

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

Re: Percentage Calculation wrong in Total Column

steveniow — Mon, 25 Nov 2019 12:49:39 GMT

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

Re: Percentage Calculation wrong in Total Column

sunny_talwar — Mon, 25 Nov 2019 13:12:11 GMT

May be this

[#]/sum(TOTAL <WardCode> PWCount)

Re: Percentage Calculation wrong in Total Column

steveniow — Wed, 27 Nov 2019 10:52:49 GMT

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