topic Weird Customer Request, is this possible?? in QlikView

Weird Customer Request, is this possible??

Tue, 06 Mar 2012 18:34:19 GMT

Hi guys, is it possible to get a row minus a row if is a calculated value in a pivote table??? I mean there is a value already but I need to substract the value of other row, any idea??

its just a substraction but i can´t figure it out jet.

In other words, what I need is the that the value in Row "CUOTA NOMINA" minus value Row "NOMINA_HRAS" , the value resultant will replace the value in Row "CUOTA NOMINA".

I know is kind of weird but its a customer request.

I hope I explained myself.

Thank you for your time!!

I attached the app and a power point to explain better myself.

Weird Customer Request, is this possible??

swuehl — Tue, 06 Mar 2012 19:45:28 GMT

I think you get what you ask for using:

=sum( aggr(IF(ACCOUNT_NAME = 'NOMINA_HRAS', sum({$<ACCOUNT_NAME = {'MINA_HRAS'}>}[Costo por hora]* [Hrs Reales]),

SUM(Gastos)),ACCOUNT_NAME,COCE_NAME))

sum({$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales])

if(ACCOUNT_NAME = 'CUOTA NÓMINA',sum(total {$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales]),0)

Not sure if there is not a better way to do all this, but since I don't understand what you are calculating here, I just can't say.

Hope this helps,

Stefan

Weird Customer Request, is this possible??

Tue, 06 Mar 2012 21:29:05 GMT

Yes Stefan, that´s exactly what I needed thank you, but there is just one think missing, the Total is not the correct value, is not doing the Sum.

Weird Customer Request, is this possible??

swuehl — Tue, 06 Mar 2012 23:20:12 GMT

Right, you need to use an additional advanced aggregation:

=sum( aggr(IF(ACCOUNT_NAME = 'NOMINA_HRAS', sum({$<ACCOUNT_NAME = {'MINA_HRAS'}>}[Costo por hora]* [Hrs Reales]),

SUM(Gastos)),ACCOUNT_NAME,COCE_NAME))

sum({$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales])

sum(aggr(

if(ACCOUNT_NAME = 'CUOTA NÓMINA',sum(total {$<ACCOUNT_NAME = {"NOMINA_HRAS"}>}[Costo por hora]* [Hrs Reales]),0)

,ACCOUNT_NAME,COCE_NAME))

Weird Customer Request, is this possible??

Tue, 06 Mar 2012 23:24:25 GMT

Wow!! yes Stefan it works perfectly, Thank you very much, you are the man!!