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: 
mjperreault
Creator
Creator

Showing Text Representation of Dual in a aggregation

I have data such as the below

RatingNumericRating
Aaa1
Aa23
A15
A26
A37
Baa18
Baa29
Baa310
Ba111
Ba212
Ba313
B114
B215
B316
Caa117
Caa218
Caa319
Ca20
C21
D23
NR24


I created a field in my application RatingDual that is Dual(Rating,NumericRating)

I want to be able to calculate an floor(avg(RatingDual)) across a population and represent that as the the textual Rating value.

Ex. Say I have a population of three rows

IDRatingNumericRating
AAaa1
BA26
CBaa310

 

In a KPI I have the expression floor(avg(RatingDual)) which yields 5 but I want it to show "A1"

Is this possible without having to create a large pick match expression? 

 

Thanks,
Mark

Labels (4)
2 Replies
mjperreault
Creator
Creator
Author

@sunny_talwar any thoughts?

sunny_talwar

Do you have a list of the ratings brought into the dashboard in addition to rating by ID? If you do, you can always use an if statement

Aggr(
  If(Floor(Avg(RatingDual)) = MasterNumericRating, Rating)
, Rating)

Here MasterNumericRatingwill be from an Island table which will be made up of Rating as MasterRating and NumericRating as MasterNumericRating. You don't want this to join any of your tables in the database