Total in Pivot qliksense

[sowmi]

Hello all,

I am using pivot table based on calculations ,i have negative values ,so i need to replace negative values with zero,So i did within pivot table and showing zero for negative values ,And additionally  i need to display total so what i am facing is In TOTAL'S ,it is summing up negative values instead of "Zero",

For example :  O/p from pivot   expecting o/p

                             -1                              0

                             -2                              0

                               1                              1

                             2                                 2 

Total :                0                                 3

 Can Someone Help

Thanks                                          

[rubenmarin]

Hi, Rangemax funtion returns the higher on any value, so you if your expression is Sum(Sales) you can use RangeMax(0, Sum(Sales)), but this won't work for totals, to make this work for totals you can add an aggr so the total sums each row indiviually:

Sum(Aggr(RangeMax(0, Sum(Sales)), Dimension1, Dimension2...) // Dimension1,Dimension2 are the fields used as dimension in the table.

Another possiblibity is using set analysis, like:

- Sum({<Sales={">=0"}>} Sales)

- Sum({<Customer={"=Sum(Sales)>0"}>} Sales) // Only sums customers with sum of sales higher than 0

[PrashantSangle]

Hi,

try below

if(sum(Sales)>0,Sum(Sales),0)

Regards,

[sowmi]

Hi,
I used IF condition,in table it is replacing negative value with zero ,when I am adding default total in pivot table instead of zero it is summing up both negative and positive values and displaying total Values

[Rohan]

Hello,

Instead of handling this in the front end, create a new dimension to ignore the negative values using the above if condition in back-end & then use this new dimension  as a measure. May be that'll help.

Regards,

Rohan.

[Kushal_Chawda]

@sowmi  only if condition won't work here. You may need to apply aggr function  for total to work it properly.  Try below

sum(aggr(if(sum(Sales)>0,sum(Sales),0),Dimension1, Dimension2))

where Dimension1, Dimension2 are your pivot table dimensions. 

[sowmi]

Hi Kush,

If my calculation sum(Sales) has only positive values then sum(aggr(if(sum(Sales)>0,sum(Sales),0),Dimension1, Dimension2)) doesn't work?

[Kushal_Chawda]

It should work if you have only positive values 

[sowmi]

I am using valuelist for one of the dimensions,If i use your code i am only getting values for the column for remaining it is showing zero .

[Kushal_Chawda]

What is actual valuelist expression and measure you are using?