topic Re: Re: Re: Re: Substract by in QlikView

Substract by

pgalvezt — Wed, 15 Jan 2014 17:13:18 GMT

Hello,

I have the following formula:

(sum({<Cuenta={'01020304'}>}Monto_Pesos) /Valor_Factor) - (sum({<Doc={'AM130311000005','AM130311000006'}>}Monto) /Valor_Factor).

There are many more "AM"

In the pivot Appear.

Doc Monto

A 5000

B 10000

So on

AM130311000005 -90000

AM130311000006 -98777

I would like if its possible subtract all documents beginning with "AL" (Because there are many "AL"). This negative value is shown in the chart but would not appear in the pivot. Only in the final result.

Thanks!

Re: Substract by

Clever_Anjos — Wed, 15 Jan 2014 17:15:36 GMT

maybe (sum({<Cuenta={'01020304'}>}Monto_Pesos) /Valor_Factor) - (sum({<Doc={'AM*'}>}Monto) /Valor_Factor)

Re: Substract by

Wed, 15 Jan 2014 17:15:41 GMT

you can try to use wild cards in set analysis:

(sum({<Cuenta={'01020304'}>}Monto_Pesos) /Valor_Factor) - (sum({<Doc={'AM*','AM*'}>}Monto) /Valor_Factor).

Re: Substract by

pgalvezt — Wed, 15 Jan 2014 17:34:49 GMT

Hi Thank you for your replies,

I tried with AM* 7 times but QlikKview gave another value.

Total 300000 With AM* Gives 2580000

Is there a way to call just one time "AM" because in the future into the database it will aggregate more AM. I dont want to put AM every time in the formula.

Thanks,

Re: Substract by

iktrayanov — Wed, 15 Jan 2014 17:56:37 GMT

Maybe you can create a flag when you are loading you data

Load

Doc,

Monto,

if (left (Doc,2) = 'AM', 1 ,0) as AM_Flag

....

then use this in your formula.

Re: Re: Substract by

pgalvezt — Wed, 15 Jan 2014 19:16:27 GMT

Ok,

Now QlikView Gives a Negative value but the total is correct.

How can I Hide that Row.

Re: Re: Substract by

iktrayanov — Wed, 15 Jan 2014 19:23:19 GMT

Maybe suppress null values

Re: Re: Substract by

pgalvezt — Wed, 15 Jan 2014 19:35:29 GMT

But If I suppress the null values the total will change. Is there another way to hide that row?

Thanks!

Re: Re: Substract by

iktrayanov — Wed, 15 Jan 2014 19:46:17 GMT

The only other way to hide rows I can think of is using dimension limits based on your first expression.

Re: Re: Substract by

iktrayanov — Wed, 15 Jan 2014 19:52:24 GMT

Sorry this wont work with pivot table.

Re: Re: Substract by

iktrayanov — Wed, 15 Jan 2014 19:55:00 GMT

You can suppress nulls and redefine your totals using dimensionality().

Re: Re: Substract by

pgalvezt — Wed, 15 Jan 2014 19:58:40 GMT

Can you explain that with more details please,

Thanks!

Re: Re: Re: Substract by

iktrayanov — Wed, 15 Jan 2014 20:54:59 GMT

Take a look at this example.

I am hiding the row for A but my total still includes all values.

Hope that helps.

Re: Re: Re: Substract by

pgalvezt — Thu, 16 Jan 2014 12:21:24 GMT

Hi Ivan,

Thank you for your reply:

QlikView Gives me a null value this is my formula:

If(Dimensionality() = 0, Sum(Total sum({<Cuenta={'01020304'}>}Monto*-1 /Valor_Factor)),sum({<Cuenta={'01020304'}>}Monto*-1 /Valor_Factor))

What do I doing Wrong?

Dimensinality expression gives me 2.

Thanks!

Re: Substract by

iktrayanov — Thu, 16 Jan 2014 13:54:41 GMT

Can I see a snapshot?

Re: Re: Substract by

pgalvezt — Thu, 16 Jan 2014 14:08:03 GMT

Hi Ivan,

I put 2 snapshot one of them is with Dimensinality.

Thanks!

Re: Substract by

nizamsha — Thu, 16 Jan 2014 14:13:37 GMT

=(sum({$<category={"*"}>}Amount)/Count({$<category={"ID1","ID2"}>}DISTINCT Orders))-

(sum({$<category={"*"}>}Amount)/Count({$-<category={"ID1","ID2"}>}DISTINCT Orders))

it will work change according to ur formula

Re: Re: Substract by

pgalvezt — Thu, 16 Jan 2014 14:21:35 GMT

Hi Nizam,

=(sum({$<Cuenta={"01020304"}>}Monto)/Count({$<Cuenta={"01020304"}>}DISTINCT Monto))-

(sum({$<Cuenta={"01020304"}>}Monto)/Count({$-<Cuenta={"01020304"}>}DISTINCT Monto))

Gives Null Values,

Re: Substract by

iktrayanov — Thu, 16 Jan 2014 16:40:45 GMT

I think you have extra Sum() in your expression

Try this

If(Dimensionality() = 0, Sum(Total{<Cuenta={'01020304'}>}Monto*-1 /Valor_Factor),sum({<Cuenta={'01020304'}>}Monto*-1 /Valor_Factor))

If this doesn't work I wold test each expression separately first and then put into If statement.

Re: Re: Re: Substract by

pgalvezt — Thu, 23 Jan 2014 18:47:52 GMT

Hello Ivan,

Is Ok this formula?

if (Dimensionality() = 0,

Sum(total sum({<Cuenta={'31010101'}>}Monto*-1 /Valor_Factor),sum(sum({<Cuenta={'31010101'}>}Monto*-1) /Valor_Factor)))

This gave me an error!

I dont know if Sum(total with Set analysis works of the same way that Sum(Total Value) like your formula:

if (Dimensionality() = 0,

Sum(total Value),sum(Value))

QlikView don't recognize the total as a field. Could be that?

Thanks!