QlikView - evaluate expression in chart object

[agigliotti]

Hello,

I have a pivot table with a dynamic expression as below:

if( t01_dims_y._field = 'FRC min',

'Min(',

if( t01_dims_y._field = 'FRC med',

'Avg(',

if( t01_dims_y._field = 'FRC max',

'Max(' ) ) )

& 'Aggr(' &

if( t01_dims_x._field = 'RCV',

'sum(MovimIndustriale.ImportoUdc)',

if( t01_dims_x._field = 'CST',

'sum(1)' ) )

& ', %CodCommessa ))'

it give me a valid expression as string.

How does is possible to evaluate it ?

Before I was trying the below expression but it doesn't works and I don't understand why:

if( t01_dims_y._field = 'FRC min' and t01_dims_x._field = 'RCV',

Min( Aggr( sum(1), %CodCommessa ) ),

if( t01_dims_y._field = 'FRC max' and t01_dims_x._field = 'RCV',

Max( Aggr( sum(1), %CodCommessa ) )

))

without using Aggr function it works !?

the same behaviour occurs also using ValueList as dimension.

Thanks in advance for your help.

Best regards

Andrea

[marcus_sommer]

If your calculation goes over a single value of "t01_dims_y._field" because it is always one selected the you could use:

$(=YourExpressionString)

If not and you want to calculate it on a row-level of "t01_dims_y._field" then it won't be possible in this way because the $-sign expansion creates an adhoc-variable which could only have one value at the same time.

The only practically way to handle such cases is the following logic:

pick(match(t01_dims_y._field, 'FRC min', 'FRC med', 'FRC max'),

     expr1, expr2, expr3)

- Marcus

[agigliotti]

as I said above I already tried to use the following logic:

if( t01_dims_y._field = 'FRC min' and t01_dims_x._field = 'RCV',

Min( Aggr( sum(1), %CodCommessa ) ),

if( t01_dims_y._field = 'FRC max' and t01_dims_x._field = 'RCV',

Max( Aggr( sum(1), %CodCommessa ) )

))

but it doesn't works.

it seems due by using the Aggr function in expr1/expr2/expr3.

Why?

[marcus_sommer]

With the given information it's not possible to say what didn't work like you expected it especially because you only says that it didn't work - but is there an error, 0 result or a wrong result?

It might be that you didn't need this aggr or that you used the wrong dimensions for it or anything else isn't suitable.

- Marcus

[agigliotti]

the first If statement give me the correct result while all next if give me NULL value.

[marcus_sommer]

Maybe there is no further matching as the first condition ...

if( t01_dims_y._field = 'FRC min' and t01_dims_x._field = 'RCV',

Min( Aggr( sum(1), %CodCommessa ) ),

if( t01_dims_y._field = 'FRC max' and t01_dims_x._field = 'RCV',

Max( Aggr( sum(1), %CodCommessa ) ),

'the second if returned never true and it is no further else-branch defined'

))

- Marcus

[agigliotti]

the second condition match because if a replace Max(Aggr(....  with sum(1) it show me 1 as result.

also if a move the second expression Max(Aggr(.... inside the first IF condition it show me the correct result.

it sound like a bug for me.

[marcus_sommer]

Just try it by evaluating your conditions:

if( t01_dims_y._field = 'FRC min',

     'A', if( t01_dims_y._field = 'FRC max',  'B', 'C'))

- Marcus

[agigliotti]

I just try it and it works as expected.

i'm going to move this discussion in Qlik Bug.

[agigliotti]

please mtostalwar1stevedarkloveisfail‌ take a look at this issue.

Thanks

Andrea