Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
ok,how to generate the correct report using 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
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
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
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
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
see my post above
or you could also try with AGGR funtion