Problem with TOTAL

[pgalvezt]

Hello!

I have a Pivot Table that shows Sales, Costs and %

I need to do some adjustments in the total because the operation do changes that affects the final margin. So first of all I had this

sum({<Cuenta={'31010201'}>}Costo (Works Fine)

If(N°_Transaccion='2557655',((sum({<Cuenta={'31010201'}>}Costo))*-1),(sum({<Cuenta={'31010201'}>}Costo))) (Works Fine)

QlikView do the minus but the total is the same. No chanching in the total.

What could it be?

Thanks!

[swuehl]

The N°_Transaccion is probably ambiguous in the context of the total, so QV will always evaluate the THEN branch of your if() statement.

Try

=sum( {<Cuenta={'31010201'}>} Costo * If( N°_Transaccion='2557655', -1,1))

[pgalvezt]

Hi swuehl Thank you for your reply.

Value remains positive also makes an odd subtraction.

Thanks!

[swuehl]

Could you post more details about your setting, like a small sample application, and what you expect to get?

[pgalvezt]

Hi swuehl my mystake I was selected another field. Works fine now.

How could add more to the subtraction equation.

I have this:

=sum( {<Cuenta={'31010201'},Factura-={'JL130409000024'}>} Costo * If( N°_Transaccion='2569419',  -1,1)) (Works very well)

My final result is:

241435272.

But now I need to left:

Value              N°_Transaccion

36683	2526918
138236	2557275

So,

241435272 - 36683 - 138236 = 231260353

I tried this:

=sum( {<Cuenta={'31010201'},Factura-={'JL130409000024'}>} Costo * If( N°_Transaccion='2569419' or '2526918' or '2557275',  -1,1))

But Doesn't work.

Thanks for your help.

[swuehl · Accepted solution]

Either

=sum( {<Cuenta={'31010201'},Factura-={'JL130409000024'}>} Costo *

If( N°_Transaccion='2569419' or N°_Transaccion= '2526918' or N°_Transaccion = '2557275',  -1,1))

or

=sum( {<Cuenta={'31010201'},Factura-={'JL130409000024'}>} Costo *

If( match(N°_Transaccion,'2569419','2526918', '2557275'),  -1,1))

or maybe

=sum( {<Cuenta={'31010201'},Factura-={'JL130409000024'}, N°_Transaccion -= {'2569419','2526918','2557275'} >} Costo)

- sum( {<Cuenta={'31010201'},Factura-={'JL130409000024'}, N°_Transaccion = {'2569419','2526918','2557275'} >} Costo)

[pgalvezt]

Many Thanks Works Nice.