topic Re: Calculate Avg in QlikView

Calculate Avg

Anonymous — Fri, 11 Dec 2015 14:57:55 GMT

I need help in calculating the Overall Close Score based on the Category Score calculations.

Looking to do it in a TEXT OBJECT since it will be just a single value.

Table expressions:

SLA Category(Critical-High-Medium-Low)

=count(DISTINCT {<[SLA Impact]={"Critical"}>}ID)

=count(DISTINCT {<[SLA Impact]={"High"}>}ID)

=count(DISTINCT {<[SLA Impact]={"Medium"}>}ID)

=count(DISTINCT {<[SLA Impact]={"Low"}>}ID)

Points Deducted

=(Critical *(-10)) + (High *(-5)) + (Medium *(-3))

Category Score

=(1) + ([Points Deducted]/(100))

Need to calculate an Overall Close Score

Category Scores/5

ie: 97+94+100+100+97= 488

488/5 = 97.6%

Re: Calculate Avg

sunny_talwar — Fri, 11 Dec 2015 15:03:07 GMT

May be this in the text box object:

Avg(Aggr(

(1) + ((count(DISTINCT {<[SLA Impact]={"Critical"}>}ID)*(-10)) + (count(DISTINCT {<[SLA Impact]={"High"}>}ID)*(-5)) + (count(DISTINCT {<[SLA Impact]={"Medium"}>}ID)*(-3))/(100)), [SLA Category]))

Re: Calculate Avg

Anonymous — Fri, 11 Dec 2015 15:23:58 GMT

Sunny T,

I tried this in a Text Object and the result was -2.3633333, which I believe averaged out the (-negative) amount calculated based on the Count and not the average of all 5 calculated Category Scores.

Dan

Re: Calculate Avg

sunny_talwar — Fri, 11 Dec 2015 15:33:04 GMT

Try this:

Avg(Aggr(

((1) + ((count(DISTINCT {<[SLA Impact]={"Critical"}>}ID)*(-10)) + (count(DISTINCT {<[SLA Impact]={"High"}>}ID)*(-5)) + (count(DISTINCT {<[SLA Impact]={"Medium"}>}ID)*(-3))/(100))), [SLA Category]))