Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

How to suppress ZERO values in pivot table

Hi folks,

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

Pivot 1:

ClientPrice APrice BDifference
Client A50-5
Client B550
Client C550
Client D50-5
Total2010-10

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

Result Pivot:

ClientPrice APrice BDifference
Client A50-5
Client D50-5
Total100-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

6 Replies
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

Capture.PNG

Anonymous
Not applicable
Author

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:

ClientPrice APrice BDifference
Client A1.713.336,16000000011.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
Not applicable
Author

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