Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

rrodriguesrr
New Contributor III

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

Tags (1)
6 Replies

Re: How to suppress ZERO values in pivot table

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

rrodriguesrr
New Contributor III

Re: How to suppress ZERO values in pivot table

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

Re: How to suppress ZERO values in pivot table

Try using the Round() function

Round(Price A - Price B) <> 0

rrodriguesrr
New Contributor III

Re: How to suppress ZERO values in pivot table

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

Re: How to suppress ZERO values in pivot table

try this also

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

Re: How to suppress ZERO values in pivot table

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

Community Browser