Difference in Totals between Straight Table and Pivot Table

[jlongoria]

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

];

[Gysbert_Wassenaar · Accepted solution]

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.

[buzzy996]

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. 

[jlongoria]

Yes, I want to show the 0 values.

[Gysbert_Wassenaar]

Then use the second expression with the aggr.

[jlongoria]

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")