Hi all.
I'm experimenting a problem in using filtering and ranking features in a bar-chart.
I have a category (CAT1) with more than 5000 occurrences and 2 measures (M1 and M2).
I need to show in a bar-chart the worst 10 items (not missing) according M1, but for which M2 >=5.
The problem is that with these filters and ranking I always have an empty chart.
I do some attemps and it seems as ranking is applied before filtering and not aftes (as I need!!!)
Can anyone help me?
Thanks a lot in advance!!!
Hi there,
maybe you can first create a new calc field M1_1 that only fills when M2 => 5.
Now you can use this measure in the bargraph and rank it.
greetings Peter.
Hi...
Thank you for your answer.
I've already tried an IF/ELSE statement with a new measure M1_1 with Missing in ELSE statement, but it doesn't work.
Now I try setting an out-of-scale value (in my much more big than max value, because I need to show the most little values) for M1_1 and in this case it works!!!
My be "Missing" value is not suit for classifications...
Thank you very much indeed!!!
As I understand it, the problem is that "Bottom" rankings in VA include missing values as the least values, so your calculated item,
IF M2 => 5
RETURN M1
ELSE .
Gives all missing values as its "Bottom 10" rank.
Is that what you are seeing?
If so, you might try adding a filter on the calculated measure and deselecting the "Include missing values" checkbox.
Or as a filter expression,
'M1_1'n NotMissing
I believe that should give you the desired result.
Thanks,
Sam
I've already tried, but it looks like VA ranks records before filtering.
So I use an out-of scale value for records for which M2 < 5.
Thanks a lot, anyway
Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.
Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.