Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Might be easy to see if you can share a sample
attached the sample app.
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'),
))
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?
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'),
))
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')
Not entirely sure, but when I try them in the text object... they seem to match with what I have in the table
here in text box you find the correct results.
What expressions did you use? oh... you might have used the ones without the total, right?
yes of course.