topic Re: Pivot table Total in QlikView

Pivot table Total

Fri, 31 Jul 2015 16:00:00 GMT

Hi Everyone,

I am using a pivot table and I am tying to include below expression.

=(If(Sum(CASE) >0 and PROD='C',Count(DISTINCT MATERIAL),0))

The expression works correctly on every row, but the total is not correct. The total includes everything which is not satisfied by the if condition. Can you please let me know, the correct way? Thanks in advance.

Thanks

Santhosh

Re: Pivot table Total

settu_periasamy — Fri, 31 Jul 2015 16:12:47 GMT

can you tell us the dimensions? post the sample also

Re: Pivot table Total

Gysbert_Wassenaar — Fri, 31 Jul 2015 16:13:41 GMT

At the chart level, i.e. the total level, PROD has no single value. That's why that part of the condition will never be true at the total level. If you want to sum the counts of the row levels you need to use something like:

sum(aggr((If(Sum(CASE) >0 and PROD='C',Count(DISTINCT MATERIAL),0)), Dim1, Dim2, ..., DimN)

Replace Dim1, Dim2, ..., DimN with the names of the fields that you use as dimensions in your pivot table.

Re: Pivot table Total

sunny_talwar — Fri, 31 Jul 2015 16:14:43 GMT

Try this:

=Sum(Aggr((If(Sum(CASE) >0 and PROD='C',Count(DISTINCT MATERIAL),0)), YourChartDimensions))

Re: Pivot table Total

Tue, 04 Aug 2015 02:33:01 GMT

Thanks Settu, Sunny and Gysbert. The solution worked.