topic Straight Table vs Pivot Table in QlikView

Straight Table vs Pivot Table

brindlogcool — Thu, 14 Mar 2013 11:10:25 GMT

Hi ,

I have created a straight table and have chosen Sum of rows as total. And the total is showing correctly. But when i change that straight table to the pivot table the value is getting reduced. I am not changing any expression or dimension it is like a fast change.

My expression is set analysis with count distinct.

=COUNT(DISTINCT {<Id={50}>} cid)

Re: Straight Table vs Pivot Table

Gysbert_Wassenaar — Thu, 14 Mar 2013 11:17:11 GMT

Yes, the pivot table doesn't have a totalling mode you can set, so it defaults to the expression itself. You'll have to use the aggr function if you want the sum. sum(aggr(count distinct {<Id={50}>} cid, MyDim1, MyDim2,... )). Replace MyDim1, MyDim2 ... with all the dimensions of your pivot table.

Re: Straight Table vs Pivot Table

brindlogcool — Thu, 14 Mar 2013 12:51:16 GMT

Thanks Gysbert,

I have modified the expression with aggr function But still it is not giving the same total.

Re: Straight Table vs Pivot Table

Gysbert_Wassenaar — Thu, 14 Mar 2013 12:52:59 GMT

Works for me. See attached qvw. If you're still unable to solve the problem, post your document (or a representative sample).

Re: Straight Table vs Pivot Table

brindlogcool — Thu, 14 Mar 2013 13:03:15 GMT

Thanks for your response. One more reason is inconsistent across the columns only one column is giving wrong value when converting from Staright to pivot table. But all the other columns are correct.

But if i use the agg function i am getting the wrong value for all the columns.