topic Re: Pivot table totals incorrect in QlikView

Pivot table totals incorrect

lee_williams — Tue, 19 Sep 2017 14:15:06 GMT

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

Re: Pivot table totals incorrect

sunny_talwar — Tue, 19 Sep 2017 14:58:32 GMT

Try this

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

Re: Pivot table totals incorrect

Anil_Babu_Samineni — Tue, 19 Sep 2017 14:59:37 GMT

May be this?

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

Re: Pivot table totals incorrect

lee_williams — Wed, 20 Sep 2017 08:06:27 GMT

Thank you both , worked perfectly

Re: Pivot table totals incorrect

Anil_Babu_Samineni — Wed, 20 Sep 2017 15:26:43 GMT

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