Substract by

[pgalvezt]

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!

[iktrayanov]

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

[pgalvezt]

Can you explain that with more details please,

Thanks!

[iktrayanov]

Take a look at this example.

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

Hope that helps.

[pgalvezt]

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!

[iktrayanov]

Can I see a snapshot?

[pgalvezt · Accepted solution]

Hi Ivan,

I put 2 snapshot one of them is with Dimensinality.

Thanks!

[nizamsha]

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

[pgalvezt]

Hi Nizam,

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

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

Gives Null Values,

[iktrayanov]

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.

[pgalvezt]

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!