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!

[Clever_Anjos]

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

[#3]

you can try to use wild cards in set analysis:

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

[pgalvezt]

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,

[iktrayanov]

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.

[pgalvezt]

Ok,

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

How can I Hide that Row.

[iktrayanov]

Maybe suppress null values

[pgalvezt]

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

Thanks!

[iktrayanov]

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

[iktrayanov]

Sorry this wont work with pivot table.