Hi All,
I have a pivot table which is pulling in data from a SQL table which is essentially showing a number of products as a dimension and doing a count on the products across the year.
Now on our system we had to return a few of the products c in June and this didn't show up in the original data as it only shows sales not returns. To get round this I put an extra line on the table as a placeholder and gave it a value of 1.
On creating the pivot I did a calculation in the expression ( see expression under table)
| Jan | feb | mar | Apr | May | Jun | Jul | Aug | Sep |
| product a | 1 | 1 | 1 | 1 | 1 | 1 | | | |
| product b | | 2 | 3 | 4 | 3 | 3 | 3 | | |
| product c | 2 | 2 | | | | -4 | | | |
| product d | 1 | 1 | 1 | 1 | 1 | | 1 | | |
| product e | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | |
| product f | | | | 3 | 3 | 3 | 3 | 3 | 3 |
| total | 6 | 8 | 7 | 11 | 10 | 10 | 9 | 5 | 3 |
if(match(Product,'product c') and Year = 2017 and Month = 6,count(distinct btlPOKeyCode) - 5, count(distinct btlPOKeyCode))
The result is that the table itself is showing exactly the numbers I want it to show with the -4 in june for product C , however , the totals are incorrect for this column and instead of using the -4 as specified it is using the original 1 placeholder , therefore instead of being 5 in the total it states 10.
I can't put extra data in the original import as the number is actually 3500 and I'd rather not another 3500 lines on the table so was looking at a simple way to do it post import.
Any ideas?
Cant attach original table , sorry
