topic Re: Total row in pivot table with null value in QlikView

Total row in pivot table with null value

DNeri — Sun, 26 Jan 2020 15:18:45 GMT

Hi,

I have an expression as follow:

=if(dim=a, 123*sum(time), if(dim=b, 234*sum(time), if(dim=c, 345*sum(time)))).

I understand that for the total, it cannot calculate the expression due to the fact that there is not a specific dimension, but is there a way around?

Thank you!

Re: Total row in pivot table with null value

Vegar — Sun, 26 Jan 2020 15:44:38 GMT

You could change the total from expression total to sum of rows.

Or maybe it can be solved by changing to this expression.

Pick(Match(dim,a,b,c) 123, 234, 345) * sum(time)

Re: Total row in pivot table with null value

DNeri — Sun, 26 Jan 2020 17:05:46 GMT

Thank you Vegar for your quick response.

I cannot change the total from expression to total of rows, because that option in my case is greyed out.

I thank you for teaching me this new function (Pick and Match) instead of multiple IF statements, I do prefer it.

Unfortunately, in the Total is still showing Null as value.

Re: Total row in pivot table with null value

Vegar — Sun, 26 Jan 2020 22:04:25 GMT

Take a look at the attached qvw file. I've created an example where I have two working expressions. One using pick match and one using your nested if expression. (I made some adjustments to my previous expression like adding ' ' and moving the sum to include everything. I also added an aggr to your IFs)

Pick(Match())
=sum(Pick(Match(dim,'a','b','c') ,123, 234, 345) * time)

Nested IFs
=sum(aggr(if(dim='a', 123*sum(time), if(dim='b', 234*sum(time), if(dim='c', 345*sum(time)))), dim))

Re: Total row in pivot table with null value

DNeri — Mon, 27 Jan 2020 18:59:43 GMT

Thank you Vegar,

your suggestions work perfectly.

Thank you so so much!