topic Re: pick function query in QlikView

if statement issue

hopkinsc — Mon, 07 Jul 2014 14:00:54 GMT

Hi all,

my expression is..

If(Field1=$(vVariable1))'), sum(.......

vVariable1 holds a list of other variables which have the text stored in them.

I can't upload the data, and ive been trying to get a sample together but with no luck.

Thanks

hopkinsc — Mon, 07 Jul 2014 14:52:07 GMT

anyone?

marcus_sommer — Mon, 07 Jul 2014 14:52:34 GMT

Generally vVariable1 must return a list like 'A', 'B', 'C' .... and then try:

If(Pick(Field1,$(=vVariable1)) = 'Text'), sum(.......

- Marcus

hopkinsc — Mon, 07 Jul 2014 14:56:19 GMT

Thanks Marcus,

The expression i have is correct as it works for a row by row basis, but it just doesn't give a total

Mon, 07 Jul 2014 15:00:15 GMT

did you try sum of rows on the expression for total instead of expression total ?

marcus_sommer — Mon, 07 Jul 2014 15:01:04 GMT

You need to aggregate your expression like:

sum(aggr(If(Pick(Field1,$(=vVariable1)) = 'Text'), sum(.......)), Dimension1, Dimension2))

- Marcus

Anonymous — Mon, 07 Jul 2014 15:05:40 GMT

Hi,

You may have to use Aggr() function to achieve total. Something like below,

Sum(Aggr(Sum(If(Pick(Field1,$(vVariable1)) = 'Text', .......)), $(vVariable1)))

If you using different dimension in your report, you may replace $(vVariable1) with your dimension in aggregation.