topic Re: Using rank for conditional show in QlikView

Using rank for conditional show

jessica_webb — Tue, 19 Jan 2016 15:30:44 GMT

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

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

Re: Using rank for conditional show

swuehl — Tue, 19 Jan 2016 15:43:28 GMT

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.

Re: Using rank for conditional show

jessica_webb — Tue, 19 Jan 2016 15:49:53 GMT

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

Re: Using rank for conditional show

swuehl — Tue, 19 Jan 2016 16:28:51 GMT

To geht the second value:

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

Re: Using rank for conditional show

morganaaron — Tue, 19 Jan 2016 16:31:54 GMT

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..?

Re: Using rank for conditional show

jessica_webb — Tue, 19 Jan 2016 16:46:06 GMT

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

Re: Using rank for conditional show

jessica_webb — Tue, 19 Jan 2016 16:47:16 GMT

Hi Stefan,

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

Thanks again,

Jess

Re: Using rank for conditional show

jessica_webb — Wed, 20 Jan 2016 10:11:58 GMT

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

Re: Using rank for conditional show

swuehl — Wed, 20 Jan 2016 10:59:04 GMT

Try

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

Re: Using rank for conditional show

jessica_webb — Wed, 20 Jan 2016 11:07:21 GMT

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

Re: Using rank for conditional show

morganaaron — Wed, 20 Jan 2016 11:17:35 GMT

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

Should also do it - Stefan's formula is correct - this isn't an intended way to write it (" isn't used for that purpose here) but the syntax checker should like this if you're fussy about the underlines!

Re: Using rank for conditional show

jessica_webb — Wed, 20 Jan 2016 11:24:09 GMT

Thanks Aaron.

It was kind of working without the quotation marks (in fact I'd mistakenly typed it without even single quote marks at the beginning/end) - but the font was grey italic (like when you type a variable name), and I kept getting a 'Exceeded allocated memory' warning.

But with the quotation marks it's working without any issues, so thanks for the edit!

Jess