topic Re: Problem with TOTAL in QlikView

Problem with TOTAL

pgalvezt — Thu, 26 Dec 2013 17:49:54 GMT

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!

Re: Problem with TOTAL

swuehl — Thu, 26 Dec 2013 18:21:07 GMT

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))

Re: Problem with TOTAL

pgalvezt — Thu, 26 Dec 2013 18:43:46 GMT

Hi swuehl Thank you for your reply.

Value remains positive also makes an odd subtraction.

Thanks!

Re: Problem with TOTAL

swuehl — Thu, 26 Dec 2013 20:12:34 GMT

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

Re: Problem with TOTAL

pgalvezt — Thu, 26 Dec 2013 20:52:50 GMT

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.

Re: Problem with TOTAL

swuehl — Thu, 26 Dec 2013 21:12:47 GMT

Either

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

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

=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)

Re: Problem with TOTAL

pgalvezt — Thu, 26 Dec 2013 21:21:09 GMT

Many Thanks Works Nice.