Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Straight table & pivot

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?

13 Replies
Not applicable

Re: Straight table & pivot

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

Not applicable

Re: Straight table & pivot

ok,how to generate the correct report using pivot

chauhans85
Esteemed Contributor

Re: Straight table & pivot

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

again convert it into straight table

or

there may be problem in data model

Not applicable

Re: Straight table & pivot

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

aveeeeeee7en
Valued Contributor III

Re: Straight table & pivot

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

Not applicable

Re: Straight table & pivot

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

Not applicable

Re: Re: Straight table & pivot

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

chauhans85
Esteemed Contributor

Re: Re: Straight table & pivot

see my post above

chauhans85
Esteemed Contributor

Re: Straight table & pivot

or you could also try with  AGGR funtion

Community Browser