How to suppress ZERO values in pivot table

[Anonymous]

Hi folks,

It sounds a very easy issue, but I need some help. I have the following scenario:

Pivot 1:

Client	Price A	Price B	Difference
Client A	5	0	-5
Client B	5	5	0
Client C	5	5	0
Client D	5	0	-5
Total	20	10	-10

I need to suppress the clients that DIFFERENCE = 0, so having the following result Pivot:

Result Pivot:

Client	Price A	Price B	Difference
Client A	5	0	-5
Client D	5	0	-5
Total	10	0	-10

I have already tried something like "IF( PRICE B - PRICE A <> 0, SUM(PRICE A)) and  IF( PRICE B - PRICE A <> 0, SUM(PRICE B))" in order to calculate the PRICE A and PRICE B, then the Clients that difference = 0 were suppressed. However, the TOTAL kept the same as has been shown above in Pivot 1.

Could anybody help me with this?

Thanks in advance.

Rubens Rodrigues

[sunny_talwar]

Wrap Aggr() function around your expression

Sum(Aggr(If( PRICE B - PRICE A <> 0, Sum(PRICE A)), Client)

UPDATE: Are you using single expression with two dimensions or one dimension and three expressions?

UPDATE2: this seems more like three expressions and if that is true, you should be able to use straight table with its inbuilt capability to do Sum of rows

UPDATE3: find attached and see if this is what you want

[Anonymous]

Hi Sunny,

I'm almost getting there! It seems the expression you suggested above is correct, but I'm having trouble with some values, such as the example bellow:

Client	Price A	Price B	Difference
Client A	1.713.336,1600000001	1.713.336,1599999999	-0,0000000002

After applying the expression, it's considering PRICE A = 0 for some reason (I gues is something related with decimals).

Do you have some idea how to resolve it?

Thanks!

Rubens

[sunny_talwar]

Try using the Round() function

Round(Price A - Price B) <> 0

[Anonymous]

Hi Sunny,

It seems that ROUND() resolved the problem with decimals, notwithstanding the TOTAL is not right. One thing I forgot to mention is:

• I have 2 dimensions: CLIENT and YEAR;
• I have 3 expressions as you figured out.

Look at my expression for Price A:

Sum(Aggr(If(Round((Sum({< REFERENCE_DATE = [ReferenceDate_Comparative]::[REFERENCE_DATE]>}Price) - sum(Price))) <> 0, Sum(Price)), Client))

I'm using Alternate state, because Price B is a value recorded in a different period for the sime Client.

I wonder whether the issue is that I have 2 dimensions or something related with decimals yet.

Thanks,

Rubens Rodrigues

[Kushal_Chawda]

try this also

sum({<Client ={"=fabs(sum(PriceB)-sum(PriceA))>0"}>}PriceB-PriceA)

[sunny_talwar]

I don't really understand what the issue is. But if you have two dimensions, I would do add the 2nd dimension to my Aggr()

Sum(Aggr(If(Round((Sum({< REFERENCE_DATE = [ReferenceDate_Comparative]::[REFERENCE_DATE]>}Price) - sum(Price))) <> 0, Sum(Price)), Client, Year))