topic Re: Pivot Table - Off by One in QlikView

Pivot Table - Off by One

mphekin12 — Thu, 12 Dec 2013 13:37:25 GMT

I was wondering if someone could help me out. I have a pivot table and I'm trying to could the number of records that fit a criteria. If you look at the attachment, there are clearly 31 records but the total is saying there are only 30. Can anyone explain why this is happening and maybe help with a solution?

Thank you

Re: Pivot Table - Off by One

giakoum — Thu, 12 Dec 2013 13:41:10 GMT

total criteria is applied globally, while your records have dimensions. if you could post a sample, I could explain it exactly. A work around is to select sum of rows in the expression tab.

Re: Pivot Table - Off by One

mphekin12 — Thu, 12 Dec 2013 13:44:37 GMT

Ioannis,

Thanks for your reply. I cannot post a sample due to the nature of the data. I went to the Expressions tab and I cannot select Sum of Rows (it is disabled). Any other suggestions?

Re: Pivot Table - Off by One

giakoum — Thu, 12 Dec 2013 13:47:11 GMT

the issue is caused by your expression. Can you share the expression?

Re: Pivot Table - Off by One

mphekin12 — Thu, 12 Dec 2013 13:49:40 GMT

I have tried

sum(total<CustomerID1, ProductID1, [Customer Product], [Customer Name], [Start Date1], [Exp Date1]> if(IsNull([Exp Date1]) or [Exp Date1]='' or [Exp Date1] > vM1MinExpirationDate, 1, 0))

and

sum(if(IsNull([Exp Date1]) or [Exp Date1]='' or [Exp Date1] > vM1MinExpirationDate, 1, 0))

Both of them give me the same results.

Re: Pivot Table - Off by One

giakoum — Thu, 12 Dec 2013 13:54:11 GMT

that if is only working per row not in the total as there as many values for the field. You need to change the logic completely. Last try: put the expression in a variable and try again, but I doubt it will work.

Re: Pivot Table - Off by One

michael_maeuser — Thu, 12 Dec 2013 13:54:57 GMT

in a pivot table, you generally can manipulate the total line with aggr.

try

sum(aggr((sum(total<CustomerID1, ProductID1, [Customer Product], [Customer Name], [Start Date1], [Exp Date1]> if(IsNull([Exp Date1]) or [Exp Date1]='' or [Exp Date1] > vM1MinExpirationDate, 1, 0)), list here all dimensions)

Re: Pivot Table - Off by One

mphekin12 — Thu, 12 Dec 2013 13:57:25 GMT

Thank you Michael, that seemed to do the trick.