topic Re: Difference in Totals between Straight Table and Pivot Table in QlikView

Difference in Totals between Straight Table and Pivot Table

jlongoria — Thu, 23 Apr 2015 11:34:31 GMT

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

];

Re: Difference in Totals between Straight Table and Pivot Table

Gysbert_Wassenaar — Thu, 23 Apr 2015 11:40:55 GMT

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.

Re: Difference in Totals between Straight Table and Pivot Table

buzzy996 — Thu, 23 Apr 2015 11:43:39 GMT

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.

Re: Difference in Totals between Straight Table and Pivot Table

jlongoria — Thu, 23 Apr 2015 11:46:15 GMT

Yes, I want to show the 0 values.

Re: Difference in Totals between Straight Table and Pivot Table

Gysbert_Wassenaar — Thu, 23 Apr 2015 11:46:45 GMT

Then use the second expression with the aggr.

Re: Difference in Totals between Straight Table and Pivot Table

jlongoria — Thu, 07 Apr 2016 16:18:29 GMT

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