Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Not applicable

Pivot table Total

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

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Pivot table Total

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.


talk is cheap, supply exceeds demand

View solution in original post

4 Replies

Re: Pivot table Total

can you tell us the dimensions? post the sample also

MVP & Luminary
MVP & Luminary

Re: Pivot table Total

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.


talk is cheap, supply exceeds demand

View solution in original post

Re: Pivot table Total

Try this:

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

Not applicable

Re: Pivot table Total

Thanks Settu, Sunny and Gysbert. The solution worked.