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: 
cm
Contributor II
Contributor II

Rangeavg expression in gauge

Hi smart people,

Question about the Rangeavg() function...

What I'm calling "Performance Rating" in the photo below is a long Rangeavg expression, averaging several count expressions.

In the gauge, I want the average of the two entries - it should be 46.6.  Any reason why it's showing 43.0?

THANKS

CM

Picture1.png

4 Replies
Channa
Specialist III
Specialist III

may you have dimension problem

 

it is not doing rangeavg of those two ..

since ur not using any dimension in gauge just use AVG instead of RANGAVG

Channa
cm
Contributor II
Contributor II
Author

Thanks for replying, Channa.

Just averaging doesn't get me a different number.

The Rangeavg expression is very long (below). That's the expression that gets me 50.3 for Wash DC and 42.8 for Norfolk in the table (red circle). That's good. I'm very happy there.

However, in the gauge I've tried the same expression as the KPI and it's returning 43.0 when I'm filtering on those two locations. The average should be 46.6.

I also tried turning the long Rangeavg expression into a variable called "Performance Rating" and then using Avg([Performance Rating]) as the KPI expression for the gauge. No difference. Still returns 43.0.

Any clarity with that information?

THANKS

CM

 

 

 

=Rangeavg(((((Count(distinct[Event Number]))+count(distinct{<[Disposition Name] = {"EXPEDITED REMOVAL-CREDIBLE FEAR (ERCF)"} + {"EXPEDITED REMOVAL (ER)"}>} [Event Number])+count({<[Suspected Gang Affiliation Name] -= {"NONE/NULL"}>} [Event Number])+Count(distinct{<[Incident Detail]={"TSDB (Terrorist Database Hit)/B10"}>} [SIR Number])))), (([Overall Interception Rate])*100), (((count({<[Test Result] = {'Pass'}>} [Test Result])/Count([Test Result])))*100), (((count({<[IOIL Details.Incident Type] = {"POSITIVE SEARCH NO S/A/S (C)"}>} [IOIL Details.Incident Type])+ count({<[Exam Result] = {"POSITIVE"}>} [Exam Result])+Count([CAT I Violation Description])+count({<[CAT II Violation Description] -= {"NEITHER / NONE"}>} [CAT II Violation Description]))/(count([IOIL Details.Incident Number])+count([Exam Result])+count([Result])))*100), (((Count(distinct([Incident Number]))+Sum([Drug Weight (kg)])+count(distinct {<[Property Type Code] = {FEN}>} [Incident Number])+count(distinct {<[Property Type Code] = {HER}, [Drug Weight (kg)]={">=1"}>} [Incident Number])+count(distinct {<[Property Type Code] = {MAR}, [Drug Weight (kg)]={">=500"}>} [Incident Number])+count(distinct {<[Property Type Code] = {KAT}, [Drug Weight (kg)]={">=500"}>} [Incident Number])+count(distinct {<[Property Type Code] = {HAS}, [Drug Weight (kg)]={">=200"}>} [Incident Number])+count(distinct {<[Property Type Code] = {LQH}, [Drug Weight (kg)]={">=50"}>} [Incident Number])+count(distinct {<[Property Type Code] = {COC}, [Drug Weight (kg)]={">=30"}>} [Incident Number])+count(distinct {<[Property Type Code] = {MET}, [Drug Weight (kg)]={">=15"}>} [Incident Number])+count(distinct {<[Property Type Code] = {ICE}, [Drug Weight (kg)]={">=15"}>} [Incident Number])+count(distinct {<[Property Type Code] = {OPM}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {ECS}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {PCP}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {CUR}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {ODB}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {STR}, [Drug Weight (kg)]={">=2"}>} [Incident Number])+count(distinct {<[Property Type Code] = {OXY}, [Drug Weight (kg)]={">=2"}>} [Incident Number])))), (100-(((Count({<[Incident Detail]={"TSDB (Terrorist Database Hit)/B10", "Aircraft Diversion"}>} distinct [SIR Number])+Count({<[Incident Type]={"Rescue", "Other Agency Assistance", "Significant Agricultural Event"}>} distinct [SIR Number]))*10)-((Count({<[Incident Detail]={"Criminal Allegation Filed", "Exployee Arrested", "Equipment Failure", "Lost/Stolen Property", "Unintentional Discharge", "Detainee Escape"}>} distinct [SIR Number])+Count({<[Incident Type]={"Excessive Wait Time"}>} distinct [SIR Number]))*10))), (100-((Count({<[Incident Type]={"Excessive Wait Time"}>} distinct [SIR Number]))*10)))

Channa
Specialist III
Specialist III

i am not sure but may be you falling into granularity issue, find the attach xls

 

possible solution

you can have one resident load Port,Avg(rating)

you need to build gauge on top of resident table then you can over come this issue

 

 

Channa
cm
Contributor II
Contributor II
Author

Channa,

Thanks. Loading a resident table for Port Average won't work because the performance rating is dynamic. It changes daily.

It's the far right column in the table below... Rangeavg of the 7 other "scores" below.

Any other way to get the average of that far right column into a gauge?

Thanks for a ll the time you've spent on this.

 

CM

 

Picture1.png