topic Re: QlikView - evaluate expression in chart object in QlikView

QlikView - evaluate expression in chart object

agigliotti — Wed, 14 Mar 2018 14:58:04 GMT

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

Re: evaluate expression in chart object

marcus_sommer — Wed, 14 Mar 2018 15:23:26 GMT

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

Re: evaluate expression in chart object

agigliotti — Wed, 14 Mar 2018 15:33:53 GMT

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?

Re: evaluate expression in chart object

marcus_sommer — Wed, 14 Mar 2018 16:04:17 GMT

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

Re: evaluate expression in chart object

agigliotti — Wed, 14 Mar 2018 16:09:28 GMT

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

Re: evaluate expression in chart object

marcus_sommer — Wed, 14 Mar 2018 16:18:30 GMT

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

Re: evaluate expression in chart object

agigliotti — Wed, 14 Mar 2018 16:24:08 GMT

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.

Re: evaluate expression in chart object

marcus_sommer — Wed, 14 Mar 2018 16:28:59 GMT

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

Re: evaluate expression in chart object

agigliotti — Thu, 15 Mar 2018 08:31:51 GMT

I just try it and it works as expected.

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

Re: QlikView - evaluate expression in chart object

agigliotti — Fri, 16 Mar 2018 10:22:18 GMT

please mto stalwar1 stevedark loveisfail‌ take a look at this issue.

Thanks

Andrea

Re: QlikView - evaluate expression in chart object

sunny_talwar — Fri, 16 Mar 2018 11:19:28 GMT

Might be easy to see if you can share a sample

Re: QlikView - evaluate expression in chart object

agigliotti — Fri, 16 Mar 2018 13:02:55 GMT

attached the sample app.

Re: QlikView - evaluate expression in chart object

sunny_talwar — Fri, 16 Mar 2018 13:34:31 GMT

I am not entirely sure, but may be this?

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

Num(Min(Aggr($(vL.Budget.Ricavi), %CodCommessa, t01_dims_x._field, t01_dims_y._field)), '€ #.##0;-€ #.##0'),

If(t01_dims_y._field = 'FRC med' and t01_dims_x._field = 'RCV',

Num(Avg(Aggr($(vL.Budget.Ricavi), %CodCommessa, t01_dims_x._field, t01_dims_y._field)), '€ #.##0;-€ #.##0'),

))

Re: QlikView - evaluate expression in chart object

agigliotti — Fri, 16 Mar 2018 13:49:58 GMT

with data reduced app it works as expected.

I attached also the same app with all data and there the same expression doesn't works.

it's a shame!

but why?

Re: QlikView - evaluate expression in chart object

sunny_talwar — Fri, 16 Mar 2018 13:53:50 GMT

Add TOTAL

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

Num(Min(TOTAL Aggr($(vL.Budget.Ricavi), %CodCommessa, t01_dims_x._field, t01_dims_y._field)), '€ #.##0;-€ #.##0'),

If(t01_dims_y._field = 'FRC med' and t01_dims_x._field = 'RCV',

Num(Avg(TOTAL Aggr($(vL.Budget.Ricavi), %CodCommessa, t01_dims_x._field, t01_dims_y._field)), '€ #.##0;-€ #.##0'),

))

Re: QlikView - evaluate expression in chart object

agigliotti — Fri, 16 Mar 2018 14:01:48 GMT

the results are wrong.

if you create a text object with the below expressions you can see the right results.

Num(Min(Aggr($(vL.Budget.Ricavi), %CodCommessa )), '€ #.##0;-€ #.##0')

Num(Avg(Aggr($(vL.Budget.Ricavi), %CodCommessa )), '€ #.##0;-€ #.##0')

Re: QlikView - evaluate expression in chart object

sunny_talwar — Fri, 16 Mar 2018 14:08:31 GMT

Not entirely sure, but when I try them in the text object... they seem to match with what I have in the table

Re: QlikView - evaluate expression in chart object

agigliotti — Fri, 16 Mar 2018 14:16:18 GMT

here in text box you find the correct results.

Re: QlikView - evaluate expression in chart object

sunny_talwar — Fri, 16 Mar 2018 14:18:13 GMT

What expressions did you use? oh... you might have used the ones without the total, right?

Re: QlikView - evaluate expression in chart object

agigliotti — Fri, 16 Mar 2018 14:19:20 GMT

yes of course.