Set Analysis different condition values in each row

[pljsoftware]

Hello,

I have two tables, Fact and Forecast. For each Forecast Date Time in table Forecast there are more Flow Date Time in Fact table.

I have to calculate in Pivot Table a value for each single row where for each row I get the correct ForecastIDx in Fact table for ForecastID in Forecast table.

Maybe this screenshot can explain more of my problem, I want to obtain the value of column J and to have that I try to create an expression that generate for each row a expression as in column I.

Best Regards

Luca Jonathan Panetta

[rubenmarin · Accepted solution]

Hi Luca, set analysis is calculated for the whole table, not row by row, maybe you can use:

sum(If(ForecastIDx=ForecastID, Valore))

[giakoum]

valuelist and pick & match is also a good solution in these cases

see attached

[pljsoftware]

Hi Ruben,

thank a lot, this expression solves my problem but I fear that slow down too much because my table could have between 20.000 and 50.000 rows.

Have you a similar solution more fast?

Thaks a lot.

Luca Jonathan Panetta

[pljsoftware]

Hi Ioannis,

how can I use valuelist or pick & match in this case?

Thanks for you answer.

Luca Jonathan Panetta

[giakoum]

by creating a different expression for each value. See attached example in my previous post

[rubenmarin]

Hi Luca, If it helps, I usually set forecast, budget and real data concatenated in the same table, if you find a way that selections works as expected it solves many issues.

As Ioannis saids, pick is an option, it can be something like:

Pick(ForecastID,

sum({<ForecastIDx={'1'}>} Valore),

sum({<ForecastIDx={'2'}>} Valore),

sum({<ForecastIDx={'3'}>} Valore),

sum({<ForecastIDx={'4'}>} Valore),

sum({<ForecastIDx={'5'}>} Valore)

)

[pljsoftware]

Hi Ruben,

thanks, but I think is impossible for me use Pick in this case becasuse my ForecastIDx and ForecasID are the autonumer's results and the first Pick parameter have to be the position of the expression. So I can't put the number 42578 eg Pick(42578, 'sum(....)', ...., sum(...)).

Thanks too Ioannis.

Thanks a lot.

Best Regards

Luca Jonathan Panetta