Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
nisha_rai
Contributor

Pivot table total sum not working properly

Hi ,

 

Please help me to resolve the total issue in pivot table, based on the condition I want to show the sum after that in top column wise total .

Eg: if(rangesum(above(total sum(A),0,Rownno(total)))<=Sum(B),Sum(A),0)

result of total is not coming correctly.

please have a loo the screen shot.

As you see the conditional value total is not coming correctly.

Pivot.PNGPivot

14 Replies
Channa
Valued Contributor III

Re: Pivot table total sum not working properly

RangeSum(Before(Sum(Sales), 0, ColumnNo())) 

try like this

Channa
nisha_rai
Contributor

Re: Pivot table total sum not working properly

Thanks, I can use that one but after If I have one more if condition and the above expression gives the result same like sum(Sales), which I don't want,

My expression is :

if(rangesum(above(total sum(A),0,Rowno()))<Sum(B),Sum(A), if(Rangesum(above(Total Sum(A),1,Rowno()))<Sum(B),Sum(B)-Rangesum(above(Total Sum(A),1,Rowno())),0))

in that condition , I m not getting the correct grand total

Channa
Valued Contributor III

Re: Pivot table total sum not working properly

RangeSum Above will not work

above for running total by column

before is by row
Channa
nisha_rai
Contributor

Re: Pivot table total sum not working properly

but it's working fine for all row except grand total, I need the condition value expression only.

Actually I want to achieve the cumulative sum based on the column wise 

Re: Pivot table total sum not working properly

Try this may be

Sum(Aggr(
If(RangeSum(Above(Sum(A), 0, RownNo())) <= Sum(B), Sum(A), 0),
ContractKnownAs, Date))
nisha_rai
Contributor

Re: Pivot table total sum not working properly

this expression change the values

Re: Pivot table total sum not working properly

How about this

Sum(Aggr(
If(RangeSum(Above(Sum(A), 0, RownNo())) <= Sum(B), Sum(A), 0),
ContractKnownAs, (Date, (NUMERIC))))
nisha_rai
Contributor

Re: Pivot table total sum not working properly

What it mean by (Date, (NUMERIC)))), can we do cumulative sum in script(backend)

Re: Pivot table total sum not working properly

Read about (Date, (NUMERIC)) part here

The sortable Aggr function is finally here!

You can def. do it in the script too!!