Interpret Aggregated Average

[Patrick0329]

Hi all!

 

Below is the search result of my data page. I want to interpret the General Average this way,

<=75 FAILED

<=90 PROMOTED

<=94 PROMOTED WITH HONOR

<=97 PROMOTED WITH HIGH  HONOR

<=100 PROMOTED WITH HIGHEST HONOR

my General average is calculated by this formula Round(AVG,0)

 

Thank you hope someone can help...

[Queso]

Hi!

Maybe you can add another aggregate function element on the report and paste the following formula there: 

CASE 

WHEN Round(AVG([@field:Final]),0) <= 75 THEN 'Failed'

WHEN Round(AVG([@field:Final]),0) > 75 AND Round(AVG([@field:Final]),0) <= 90 THEN 'PROMOTED'

WHEN Round(AVG([@field:Final]),0) > 90 AND Round(AVG([@field:Final]),0) <= 94 THEN 'PROMOTED WITH HONOR'

WHEN Round(AVG([@field:Final]),0) > 94 AND Round(AVG([@field:Final]),0) <= 97 THEN 'PROMOTED WITH HIGH HONOR'

WHEN Round(AVG([@field:Final]),0) > 97 AND Round(AVG([@field:Final]),0) <= 100 THEN 'PROMOTED WITH HIGHEST HONOR'

ELSE ''
END

Change the Final field in the formula to the field name of your 'Final' column. Hope this helps!

[Patrick0329]

Thanks it works but the average dis appear.

I want to achieve pic no. 2 hope you can help me.

[Queso]

Hey!

No problem! Try this formula instead:

CASE 

WHEN Round(AVG([@field:Final]),0) <= 75 THEN Left((CAST((Round(AVG([@field:Final]),0)) as VARCHAR)),2) + ' - ' + 'FAILED'

WHEN Round(AVG([@field:Final]),0) > 75 AND Round(AVG([@field:Final]),0) <= 90 THEN Left((CAST((Round(AVG([@field:Final]),0)) as VARCHAR)),2) + ' - ' + 'PROMOTED'

WHEN Round(AVG([@field:Final]),0) > 90 AND Round(AVG([@field:Final]),0) <= 94 THEN Left((CAST((Round(AVG([@field:Final]),0)) as VARCHAR)),2) + ' - ' + 'PROMOTED WITH HONOR'

WHEN Round(AVG([@field:Final]),0) > 94 AND Round(AVG([@field:Final]),0) <= 97 THEN Left((CAST((Round(AVG([@field:Final]),0)) as VARCHAR)),2) + ' - ' + 'PROMOTED WITH HIGH HONOR'

WHEN Round(AVG([@field:Final]),0) > 97 AND Round(AVG([@field:Final]),0) <= 100 THEN Left((CAST((Round(AVG([@field:Final]),0)) as VARCHAR)),2) + ' - ' + 'PROMOTED WITH HIGHEST HONOR'

ELSE ''
END

[Patrick0329]

Thank you!

Formula is valid and working, only it widens the space in Final.