Pivot table totals incorrect

[lee_williams]

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

[sunny_talwar]

Try this

Sum(Aggr(If(Match(Product,'product c') and Year = 2017 and Month = 6, Count(DISTINCT btlPOKeyCode) - 5, Count(DISTINCT btlPOKeyCode)), Product, Month))

[Anil_Babu_Samineni]

May be this?

Sum(aggr(if(match(Product,'product c') and Year = 2017 and Month = 6,count(distinct btlPOKeyCode) - 5, count(distinctbtlPOKeyCode)), FieldDims))

[lee_williams]

Thank you both , worked perfectly

[Anil_Babu_Samineni]

You can spend time < 1 minute to close this thread by flag Correct Answer of Sunny's Response..