I am not sure if I have been too focussed on the details and now cannot see the obvious, but clearly I am missing something obvious. I have reduced my dataset to 2 days and attached the .qvw file to illustrate my problem. But please reply in the post as well as posting a solution .qvw as I am using a single user license and cannot open posted worksheets. License due to be signed off shortly.
The files are read in as follows:
In the Inbound Transactions tab, I have created a couple of tables - it looks messy as I am trying to debug it . . . but one of the selectors is essentially the transaction type: IMP / EXP / ADJ- / ADJ+ / TRFI / TRFO.
Now I create several table all with largely the same columns - as I only want to let the user see Inbound transactions on this tab, I use the following expression when summing the total volume: =If([TransDet TXN_TYPE]='IMP',SUM([TransDet TOTAL_VOL])). Yet I get different results from a chart using this expression as I do if I use the selector to select the transaction type and my expression is SUM([TransDet TOTAL_VOL]).
Not only do I get different totals, but I also have a different number of principals displayed and summed over. I am completely stuck and I am sure it is something very obvious! I have tried to make the transaction table extremely simple and straight forward to avoid any confusion, but I do link to a principal fact table and a date table to generate some additional information, but they are very simple sttraight forward keys.
Another problem I am seeing is if I for instance add in an additional dimension, such as SITE_CODE, into one of the tables, suddenly the number of principles is increased? How is this possible? Why?
I also see that I get inconsistent results when enabling the partial sums checkbox - sometimes it displays, other times it may only display at random rows in the pivot chart.
I know someone out there is going to be able to help me - and please explain the answer as opposed to just supplying the worksheet as I cannot yet read others worksheets.