Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am working on a report, but I have an issue which I I try to solve. I have several targets for each city which have to be compared to the actual figures. (f.e. number of car-thefts, actual 70 and target 100). Each city have to meet these targets. But next to the number of car thefts also the number of robberies, burglaries, etc. are measured and compared to the targets. So far so good....
City | type | Actual | target | Percentage | Traffic Light |
A | # Car-thefts | 70 | 100 | 70% | Green |
A | # Robberies | 60 | 50 | 120% | RED |
A | # Burglaries | 30 | 70 | 43% | Green |
B | # Car-thefts | 150 | 200 | 75% | Green |
B | # Robberies | 120 | 130 | 92% | Green |
B | # Burglaries | 150 | 135 | 111% | RED |
But when I want to see the traffic lights for each city I want to see that the most negative (highest percentage) case is being used. So for both city A and B a red traffic light. Are there possibilities to say f.e. max(Percentage) for each Type. Despite the level of aggregation is changed in QV (City instead of Type), I would like to remain the lowest level of aggregation for determining the traffic light.
So the result would be:
City | Percentage | Traffic Light |
A | 120% | RED |
B | 111% | RED |
Is this possible in a normal way, without creating a variable for each dimension?
Thanks for your help!
Hello.
See the attached sample applicaiton using aggr and rank. Let me know if this helps.
John.
Hello.
See the attached sample applicaiton using aggr and rank. Let me know if this helps.
John.
Hi John,
this is exactly what I needed, I only have to figure out how you did this, but I'll figure this out. Thanks for your help.
Regards,
Mark