Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
jlongoria
Contributor

Difference in Totals between Straight Table and Pivot Table

The attached qvw has a simple in inline table (see load script below). I first created the straight table then copied it and changed the copy to a pivot table. Why does the pivot table not show the totals correctly?

Load script:

LOAD * INLINE [

    F1, F2

    type1, subtype1

    type2, subtype1

    type3, subtype1

    type4, subtype1

    type1, subtype2

    type2, subtype2

    type3, subtype2

    type4, subtype2

    type1, subtype3

    type2, subtype3

    type4, subtype3

];

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Difference in Totals between Straight Table and Pivot Table

Because the pivot table does not have a Total Mode you can set to Sum of Rows. Try sum({<F2 ={ 'subtype2'} >}1) or maybe sum(aggr(if(F2 = 'subtype2', 1, 0),F1,F2)). Not sure if you care about showing the zero values.


talk is cheap, supply exceeds demand

View solution in original post

5 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Difference in Totals between Straight Table and Pivot Table

Because the pivot table does not have a Total Mode you can set to Sum of Rows. Try sum({<F2 ={ 'subtype2'} >}1) or maybe sum(aggr(if(F2 = 'subtype2', 1, 0),F1,F2)). Not sure if you care about showing the zero values.


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
buzzy996
Honored Contributor II

Re: Difference in Totals between Straight Table and Pivot Table

ur expression creating the problem,u set for dimension subtype2=1 else for all the dimension 0,so in ut pivot table it will shows only for subtype2=1 else 0,pivot will calculates the result based on multiple dimensions. 

Highlighted
jlongoria
Contributor

Re: Difference in Totals between Straight Table and Pivot Table

Yes, I want to show the 0 values.

Highlighted
MVP & Luminary
MVP & Luminary

Re: Difference in Totals between Straight Table and Pivot Table

Then use the second expression with the aggr.


talk is cheap, supply exceeds demand
Highlighted
jlongoria
Contributor

Re: Difference in Totals between Straight Table and Pivot Table

I forgot I posted this. I really didn't know what I was doing.

With much more QlikView experience now I wouldn't do what I posted. In any case, thank you for the reply. Both of these work. While the Aggr() works as well, it is not necessary given the simple expression.

sum(if(F2 = 'subtype2', 1, 0))

and

sum({<F2 ={ 'subtype2'} >}1)  // uses set analysis (better than "if")