topic Re: Percentage calculation when using Max function in QlikView

Percentage calculation when using Max function

kkkumar82 — Wed, 07 Dec 2016 18:00:57 GMT

All,

I have faced a issue today on the percentage calculation issue, where in the numerator is a sum value when as the denominator is a max

value, the individual row value percentages are looking correct but the grand totals were incorrect.

Just attaching a file, with a sample, hope it explains my problem.

Re: Percentage calculation when using Max function

vinieme12 — Wed, 07 Dec 2016 19:12:46 GMT

The Expression is correct

This is how the Expression total for MAx is being calculated

Max(MarksScored) = 89

Max(Marks) = 100

so 89%

if for any of the subjects max marks were 200 then Expression Total would show = 89/200

Hope this helps

Re: Percentage calculation when using Max function

sunny_talwar — Wed, 07 Dec 2016 19:17:07 GMT

Or this:

Marks Scored

Max(Aggr(Sum(MarksScored), Sno))

Max Marks

Max(Aggr(Sum(MaxMarks), Sno))

Re: Percentage calculation when using Max function

kkkumar82 — Thu, 08 Dec 2016 12:07:10 GMT

Hi sunny,

The original problem I have got fixed , using aggr, but the sample I have provided , if you see that, the percentage

should be 556/700 = 79.43% but because of using Max() the percentage is coming as 89%, any fix for this.

Re: Percentage calculation when using Max function

sunny_talwar — Thu, 08 Dec 2016 12:09:25 GMT

You want 79.43? I thought you wanted 89%. To get 89%, why not just use this?

Sum(MarksScored)/Sum(MaxMarks)

Re: Percentage calculation when using Max function

kkkumar82 — Thu, 08 Dec 2016 12:25:04 GMT

For this case , I can use Sum() but is there a way can we get with Max()

Re: Percentage calculation when using Max function

sunny_talwar — Thu, 08 Dec 2016 12:27:56 GMT

I have no idea what you are looking for Kiran. Do you want to expand a little on what you are doing?

Re: Percentage calculation when using Max function

kkkumar82 — Thu, 08 Dec 2016 12:32:35 GMT

As per your suggestion I can go with Sum(MarksScored)/Sum(MaxMarks) but I am asking you whether is it possible to the percentage 79 still using Max() function.

Hope I am clear this time

Re: Percentage calculation when using Max function

sunny_talwar — Thu, 08 Dec 2016 12:39:41 GMT

I am still not sure, but may be try this

Sum(Aggr(Max(MarksScored), Sno))/Sum(Aggr(Max(MaxMarks), Sno))

Re: Percentage calculation when using Max function

kkkumar82 — Thu, 08 Dec 2016 12:50:41 GMT

You picked it this time as always ..........

Re: Percentage calculation when using Max function

kkkumar82 — Thu, 08 Dec 2016 12:52:57 GMT

All,

This is only a sample I want to check how we can get the percentage correctly even we are using Max() function

The solution for the above sample pretty simple is to use Sum(MarksScored) / Sum(MaxMarks) but I was trying

whether we can get it if we still use Max() for Sum() in the above expression.

Thats all the story ends...

Thanks Sunny once again

Re: Percentage calculation when using Max function

sunny_talwar — Thu, 08 Dec 2016 12:54:17 GMT

Awesome