topic Re: Set Analysis different condition values in each row in QlikView

Set Analysis different condition values in each row

pljsoftware — Wed, 11 Feb 2015 16:08:21 GMT

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

Re: Set Analysis different condition values in each row

rubenmarin — Wed, 11 Feb 2015 16:18:41 GMT

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

sum(If(ForecastIDx=ForecastID, Valore))

Re: Set Analysis different condition values in each row

giakoum — Wed, 11 Feb 2015 16:20:06 GMT

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

see attached

Re: Set Analysis different condition values in each row

pljsoftware — Wed, 11 Feb 2015 16:49:12 GMT

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

Re: Set Analysis different condition values in each row

pljsoftware — Wed, 11 Feb 2015 16:50:37 GMT

Hi Ioannis,

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

Thanks for you answer.

Luca Jonathan Panetta

Re: Set Analysis different condition values in each row

giakoum — Thu, 12 Feb 2015 08:57:52 GMT

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

Re: Set Analysis different condition values in each row

rubenmarin — Thu, 12 Feb 2015 09:09:54 GMT

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)

)

Re: Set Analysis different condition values in each row

pljsoftware — Thu, 12 Feb 2015 11:53:59 GMT

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