Filter and Rank in VA 7.1 Report

[ita_sas]

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!!!

[PeterWijers]

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.

[ita_sas]

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!!!

[Sam_SAS]

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

[ita_sas]

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