Using rank for conditional show

[jessica_webb]

Ultimately, I want to create QV reports displayed on dynamic selections.

I've thought long and hard about how to do this, and think I might have a possible solution, but I'm struggling to implement it.

The selections will be made in a list box (which must remain in alphabetical order):

VALUES

A

B

C

D

So far, I have come up with the use of RANK to get where I need.

So, if I use the expression

IF(GetSelectedCount(VALUES)>=1, RANK(VALUES))

Then selecting A, C and D gives me

VALUES	RANK
A	1
C	2
D	3

Selecting B and D gives me

VALUES	RANK
B	1
D	2

Now, what I want to do, is use this dynamic 'Rank value' to conditionally show either an expression or a dimension.

I have tried

(VALUES='A' and IF(GetSelectedCount(VALUES)>=1, RANK(VALUES))=1)

for an expression that specifies VALUES 'A' through set analysis, and

(VALUES='B' and IF(GetSelectedCount(VALUES)>=1, RANK(VALUES))=1)

for an expression that specifies VALUES 'B' through set analysis... etc

What I would expect (or hope!) from this, is for the first selection shown (selecting A, C and D)

is that only the expression for VALUES A to work

And for the second selection shown (selecting B and D)

only the expression for VALUES B to work

What actually happens is nothing shows... If I ONLY select VALUES A, then VALUES A expression works. Or, if I ONLY select VALUES B, then VALUES B expression works. But if I select more than one VALUES, then 'All expressions disabled'.

I would like to be able to change the conditional expression for different charts, so that in some cases the expression only shows if the VALUES is 2nd, or 3rd, or 4th selected in the list... So it will (I presume) need to be based on some kind of ranking.

Some help with this, or alternative suggestions, would be greatly appreciated!

Thanks,

Jess

[swuehl]

Maybe just use conditional expressions like

//for A Expression

=If( Minstring( VALUES) ='A',1,0)

for B expression

=If( Minstring( VALUES) ='B',1,0)

etc.

[jessica_webb]

Hi Stefan,

Thanks for your reply. This works for the example I've given, however it's not quite the solution I need.

I should have explained further, but I want to then extend the expression in other charts, so that if a VALUES is second in the list, then only that expression shows.

So to take my first example (selecting A, C and D) how would I make only Cs expression show, or Ds... Basically, I want want a conditional show when a VALUES is at Nth position is the selected list.

Any ideas?

Thanks,
Jess

[swuehl]

To geht the second value:

=Subfield( Concat( DISTINCT VALUES, '|'),'|',2)

[morganaaron · Accepted solution]

Hi Jess,

You could use either the Index or the SubField functions depending on the nature of your field "Values". SubField is probably safer as it should accept anything in the VALUES field.

If you used:

SubField(Concat(Values, '|'), '|', 2) = 'C'

This would only show your chart if 'C' was the second selected value from your VALUES list. The delimiter I've used ('|') is arbitrary, you could use anything that fitted as long as it wasn't present in your VALUES field. The concat function is joining all your available selections together, then SubField splits it back out to check what the second "entry" is essentially.

Happy to explain more if that doesn't make sense, unsure if that's exactly what you're looking for..?

[jessica_webb]

Hi Aaron,

That's an absolutely perfect solution - thank you so much for your help!

Makes complete sense, and means I can stop messing round with my wildly inefficient ranking system!

Thanks again,

Jess

[jessica_webb]

Hi Stefan,

Thank you. Just needed that additional ='C' that Aaron mentioned below, and it'w working perfectly.

Thanks again,

Jess

[jessica_webb]

Hi Aaron,

Sorry - quick further question...

Is there anyway I could use the expression you suggested in set analysis? So for example, if I only wanted to count the results of a particular value, rather than saying:

Count({<VALUES={'C'}>} RESULTS)

have the expression pick the first/second/thrid... value from a selected list, something like:

Count({<VALUES={'SubField(Concat(Values, '|'), '|', 1)'}>} RESULTS)

but that actually works...

Thanks,

Jess

[swuehl]

Try

Count({<VALUES={'$(=SubField(Concat(Values, '|'), '|', 1))'}>} RESULTS)

[jessica_webb]

EDIT:

Hi Stefan,

Thanks for your help.

It doesn't seem to like that - SubField not turning blue as expected, and |'), and onwards underlined red.

Sorry, scrap that - it's working after all! It didn't like it when I copied and pasted the expression, but typing it in seems ok!

Thanks again,

Jess