topic Straight table & pivot in QlikView

Straight table & pivot

Wed, 30 Jul 2014 10:06:09 GMT

If I use straight table or pivot it show diff total in both.In straight table ,it consider the value of Int & Dom & generate correct result.But in pivot table it won't consider the Int& Dom& display total excluding value of Int & Dom

But I need the report ,as display in the pivot table.What to do?

Re: Straight table & pivot

Wed, 30 Jul 2014 10:13:32 GMT

Hi Shweta,

A pivot table will always give you the expression total, where as in a straight table you are able to specify what total you would like, in your example it looks like your straight table has been set to 'Sum of Rows' and not 'expression total'

hope that helps

Joe

Re: Straight table & pivot

Wed, 30 Jul 2014 10:17:56 GMT

ok,how to generate the correct report using pivot

Re: Straight table & pivot

SunilChauhan — Wed, 30 Jul 2014 10:22:33 GMT

you could convert staight into pivot-> go to expressiom-> select sum of rows than expression total

again convert it into straight table

there may be problem in data model

Re: Straight table & pivot

Wed, 30 Jul 2014 10:24:16 GMT

Well basically, if you do a sum of rows, any duplicate data will be calculated accordingly. However in the pivot for the total values will only calculate once.

From looking at the table I would say you have the below value against two Terminal_Type, which is counting twice in your straight table but correctly only once in your pivot.

So take a look at your underlying data model to resolve

hope that helps

Joe

Re: Straight table & pivot

aveeeeeee7en — Wed, 30 Jul 2014 10:24:57 GMT

Hi Shweta

It is not that Pivot Table is not considering Int& Dom.

The problem is that in your table there are 2 similar values of 2.10

I suggest you use Recno() in the table from where you are picking AOP YTD values and do this is Backend.

This will resolve your problem.

eg.

Load

Recno() AS Recno,

Terminal type,

Terminal_Type,

Terminal Location,

AOP YTD,

......

.....

FROM Tablename.qvd(qvd);

Regards

Aviral Nag

Re: Straight table & pivot

Wed, 30 Jul 2014 10:34:08 GMT

Hi Aviral,

based on what I think he has in the model, I don't think that will work.

I don't believe he has [AOP YTD] and Terminal_Type in the same table, as if he did, there would be two row entries for that 2.1 and the charts would calculate the same duplicated number (as each 2.1 would have a different terminal value).

He has Terminal_Type in a linked dimension table I believe, so the single row entry of 2.1 is linked to two values in his dimension table, so the expression total gives the 2.1, and sum of rows 4.2

Re: Re: Straight table & pivot

Wed, 30 Jul 2014 10:43:23 GMT

PFA

when I test that as mentioned in the attachment ,it will display the correct data in both straight and pivot table.

But it not work in my application .Is there any other way to resolve this problem at front end,as my application is quite large

Re: Re: Straight table & pivot

SunilChauhan — Wed, 30 Jul 2014 10:45:36 GMT

see my post above

Re: Straight table & pivot

SunilChauhan — Wed, 30 Jul 2014 10:46:21 GMT

or you could also try with AGGR funtion

Re: Re: Straight table & pivot

Wed, 30 Jul 2014 10:59:21 GMT

it wont work with aggr

Re: Re: Straight table & pivot

Wed, 30 Jul 2014 11:36:57 GMT

Is it possible the same structure using straight table

Re: Straight table & pivot

michael_maeuser — Wed, 30 Jul 2014 11:43:09 GMT

i don´t know your data but you can try 2 things :

sum(aggr(sum(expression), dim1, dim2, dim3))

if that doesn´t help you can try to create a single expression for the total line with dimensionality()...

if(dimensionality()=1, then ....

just add dimensionality() to your table and you will see which value the total line has

Re: Re: Straight table & pivot

Wed, 30 Jul 2014 11:49:55 GMT

if(dimensionality()=1, then ...???