Pivot Table - Off by One

[mphekin12]

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

[giakoum]

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.

[mphekin12]

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?

[giakoum]

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

[mphekin12]

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.

[giakoum]

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.

[michael_maeuser · Accepted solution]

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)

[mphekin12]

Thank you Michael, that seemed to do the trick.