topic Re: Using ValueList in Conditional Expression in QlikView

Using ValueList in Conditional Expression

Thu, 07 May 2015 16:26:25 GMT

I've created the following table where the names are a dimension, quarter 1 and quarter 2 are individual expressions (I kept them as expressions since they have different formulas and the expression labels are dynamic that get updated based on selections). The 'Actual Sales' and 'Expected Sales' are a valuelist dimension.

Basically, Actual Sales would only have quarter 1 and expected sales would have quarter 2 and other additional quarters. Since the valuelist is a dimension, all expressions show up under it. I want to add a condition for Quarter 1 along the lines of :

Valuelist ('Actual Sales', 'Expected Sales' = 'Actual Sales')

so that only quarter 1 shows up under Actual Sales and only Quarter 2 shows up under Expected Sales.

Its my first question so Im not sure if I covered everything. Let me know if you need further details to answer it.

Thanks for the help!

Re: Using ValueList in Conditional Expression

stigchel — Thu, 07 May 2015 16:35:55 GMT

You can reference the synthethic dimension by repeating it in your expression like

if(Valuelist ('Actual Sales', 'Expected Sales' )= 'Actual Sales',YourExprforQ1 , YourExprforQ2)

Re: Using ValueList in Conditional Expression

Thu, 07 May 2015 19:50:08 GMT

Hey Piet,

If I do that, I lose out on the labels Quarter 1 and Quarter 2, which I need to keep.

Re: Using ValueList in Conditional Expression

stigchel — Fri, 08 May 2015 09:43:09 GMT

For the label of the above expression you can do the same like

if(Valuelist ('Actual Sales', 'Expected Sales' )= 'Actual Sales','Q1','Q2')

Or do you want both labels for actual and expected? Then you can use two expressions like

if(Valuelist ('Actual Sales', 'Expected Sales' )= 'Actual Sales',YourExprforQ1,null())

if(Valuelist ('Actual Sales', 'Expected Sales' )= 'Actual Sales',YourExprforQ2,null())

Re: Using ValueList in Conditional Expression

Fri, 08 May 2015 15:21:31 GMT

I need to show both labels.

I tried using the second suggestion and it displays both Quarter 1 and Quarter 2 under each of Actual Sales and Expected Sales. Is there a way to hide these null() columns?

Re: Using ValueList in Conditional Expression

stigchel — Fri, 08 May 2015 19:04:00 GMT

I'm confused, my first suggestion did that, but then you said you needed to keep both labels. You can either have the one label for each or both labels with null values for Q1 or Q2. You can replace what it displays for null values in the presentation tab, but otherwise there are no options.

Re: Using ValueList in Conditional Expression

Fri, 08 May 2015 20:37:17 GMT

Sorry, I was probably bad at explaining it. This is what I'm trying to achieve:

The Q1, Q2 etc. label names are updated based on selection box selection, so each of them has a different expression. When I use the first suggestion you provided, both Q1 and Q2 label names show up under Actual Sales and under Expected Sales. I only want Q1 under Actuals.

Thanks for the help!!

Re: Using ValueList in Conditional Expression

stigchel — Sat, 09 May 2015 10:45:24 GMT

You can't have multiple expressions for this scenario. The quarter needs to be a (dynamic)dimension, the expression a single expression. Depending on how this will change when the user select things, this single expression can become a very nasty multiple nested if statement. The other option is to use the associative capabilities of the qv datamodel.

See this post, which is very similar to what you are trying to do

Extra header in Pivot