topic Re: ValueList - Expression Problem in QlikView

ValueList - Expression Problem

ljackson — Tue, 28 Oct 2014 12:26:34 GMT

I'm sure there is a simple solution to my problem, I just can't work out what it is

I have written an expression using a ValueList, which is working fine for the first two parameters on the list. When I try to add the third value it fails (the column shows nulls throughout). Can anyone see what I am doing wrong? I have removed the calculation, and just put in a simple '1' result, just to try and get the value to show on the table - at the moment I can only see the first two values (Ambient and Multi Temp)

Can anyone help?

Thank you

Re: ValueList - Expression Problem

rubenmarin — Tue, 28 Oct 2014 12:35:37 GMT

Hi Leah, after the ActWsvD there is and extra ')', remove it, you also need to remove the last ',)' line and set '))' at the end

Re: ValueList - Expression Problem

ljackson — Tue, 28 Oct 2014 12:51:30 GMT

That worked a treat

Thank you Ruben

Re: ValueList - Expression Problem

flipside — Tue, 28 Oct 2014 12:57:00 GMT

Further to Ruben's answer, you may want to experiment with using the Pick function as I feel it makes it easier to read and maintain the varying expressions. There are two methods ...

The first one is to order your expressions in reverse alphabetical rank order ...

=pick(rank(valuelist('Ambient','Multi Temp','Central Billing','Total')),
'Total Expr', //Expression to match last alphabetical rank
'Multi Temp Expr', // .. order expressions in reverse order
'Central Billing Expr', // .. as per alphabetical rank result
'Ambient Expr' //Expression to match first alphabetical rank
)

The second is to assign a numerical value to the Valuelists and change your dimension to match, resulting in the expressions listed in the correct order ...

Dimension:

=valuelist(Dual('Ambient',1),Dual('Multi Temp',2),Dual('Central Billing',3),Dual('Total',4))

Expression:

=pick(valuelist(Dual('Ambient',1),Dual('Multi Temp',2),Dual('Central Billing',3),Dual('Total',4)),
'Ambient Expr',
'Multi Temp Expr',
'Central Billing Expr',
'Total Expr'
)

Hope these help

flipside

Re: ValueList - Expression Problem

ljackson — Wed, 29 Oct 2014 09:41:31 GMT

Thanks Flipside.

I've not used the PICK function before, and I'm always looking for ways to make processes simpler to maintain (aren't we all?!).

I appreciate your input

Re: ValueList - Expression Problem

flipside — Wed, 29 Oct 2014 11:17:59 GMT

Using this method will help negate the comma and bracket confusion associated with nested ifs. It's great when you revisit very complex expressions that need tweaking months or years after being first written.

Re: ValueList - Expression Problem

sohailansari201 — Fri, 14 Aug 2015 21:37:32 GMT

Hi,

I am having a unique issue while using valuelist() in a straight table that I am unable to find any help with:

My Dimension:

Valuelist('A','B','C')

My Expression:

pick(match(valuelist('A','B','C')

'A','B','C')

,sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) > 10000, 1,0), storeID))

,sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) < 10000, 1,0), storeID))

,sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) = 0, 1,0), storeID))

)

But the Result is coming out to be:

A 54

B 0

C 0

No matter in which order I keep my 3 formulas, only the first one works and rest of them comes out as zero. However, if I create three text boxes and put these 3 formulas in each of these text boxes, I can see the results of all three formulas. (I am scratching my head ) Please help.

Thank you,

Re: ValueList - Expression Problem

ljackson — Wed, 26 Aug 2015 09:45:14 GMT

Try it without the match:

pick(valuelist('A','B','C'),

sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) > 10000, 1,0), storeID)),

sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) < 10000, 1,0), storeID)),

sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) = 0, 1,0), storeID))

)

Re: ValueList - Expression Problem

sohailansari201 — Wed, 26 Aug 2015 14:06:58 GMT

Thanks Leah, I tried but it didn't work. The results are coming out as

A -

B -

C -

Re: ValueList - Expression Problem

ljackson — Wed, 26 Aug 2015 14:19:40 GMT

Okay ....try this:

pick(valuelist('A','B','C'),

(sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) > 10000, 1,0), storeID))),

(sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) < 10000, 1,0), storeID))),

(sum(aggr(if((min({<planyear_endyear={$(=max(planyear_endyear)-1)}>} salesfigure)) = 0, 1,0), storeID)))

)

Re: ValueList - Expression Problem

ljackson — Wed, 26 Aug 2015 14:22:02 GMT

Seems strange as you said the individual expressions work in a text box .......not sure my last amend will make any difference

I found a similar thread, looks like it's something to do with the aggregation:

Multiple Aggr() in Pick/Match function