topic Re: Conditional calculation for expressions in QlikView

Conditional calculation for expressions

sibrulotte — Fri, 03 Jan 2014 20:32:28 GMT

Hi, so I built myself a sandbox.

using this tutorial:

http://livingqlikview.blogspot.ca/2012/01/conditional-dimensions-and-expressions.html

All expressions are built in the pivot I want to show, and I'm using this expression in the conditional calculation of expressions:

subStringCount(concat(Expr_ID_m,'|'),'A')

Concurrently, I have a table loaded inline table that has two fields:

Expr_m, and Expr_ID_m

A listbox of Expr_m gives me what expression I'd like to show.

It works great. But if there is no selection in thaat field... ALL expressions in the pivot show up.

That is a problem. I initially just made a condition on the pivot to show only if there was at least one selection done on Expr_m.

that works great. But I'm up to a point where I have to many expressions to show in one single listbox, I want to segregate into multiple list boxes.

Expressions for monthly, trimestre, semestre, and YTD.

The work around I found was an extra line in my load inline tables that read EMPTY, with an ID that doesn't match anything, with an automatic selection on these values on sheet activation. THAT IS UGLY.

There must be a better way to limit the expressions shown if I am using multiple listboxes.

I hope I was clear enough. I can't upload my data, but if I get really stuck, I'll try to do a mockup for this forum.

Cheers

Re: Conditional calculation for expressions

maxgro — Sat, 04 Jan 2014 22:39:27 GMT

Simon

what about adding an and condition to conditional dimension?

= subStringCount(concat(Expr_ID_m,'|'),'A') and GetSelectedCount([Expr_ID_m])> 0, 1, 0)

Regards

Re: Conditional calculation for expressions

Sun, 05 Jan 2014 02:11:38 GMT

Hi Simon,

Massimo's code can be simplified to following.

= subStringCount(concat(Expr_ID_m,'|'),'A') and GetSelectedCount([Expr_ID_m])

And, this should also work.

=Match('A', $(=Concat(distinct total chr(39)&Expr_ID_m&chr(39), ','))) and GetSelectedCount(Expr_ID_m)

Regards,

Sean

Re: Conditional calculation for expressions

sibrulotte — Tue, 07 Jan 2014 21:10:44 GMT

Those were all very helpfull hints thank you. I'm going to mark my answer as the correct as this is what led to the solution;

=subStringCount(concat(Expr_ID_m,'|'),'A') and (GetSelectedCount(_expr_m)> 0)

So 2 things:

getselectedcount was for the actual selection, which was done on the field _expr_m

and it has to pass the test >0.

EDIT:

Sean has made a good point: You don't need to test against > 0.

Sorry I can't mark it usefull after the question is marked as answered but that is a very good point.

Re: Conditional calculation for expressions

Tue, 07 Jan 2014 21:55:40 GMT

You don't need to test the condition. As long as the GetSelectedCount is not Zero, any number will pass.