9 Replies Latest reply: Sep 14, 2017 3:46 PM by Sunny Talwar RSS

    Incorrect average in totals line for pivot table

    Mike Grattan

      I'm using the following formula:


      Avg(Aggr(Count(Distinct CaseNumber), Warehouse,Year,Date))


      The pivot table comes out like this:


      pivot table average totals.jpg

      When I try to validate the totals line in Excel, I am getting different numbers:


      pivot table average totals_excel.jpg


      I am currently hiding Nulls in this table for Warehouse.  PFWarehouse is a calculated master dimension:



      If I do not exclude Nulls from the pivot table I get the following data:

      pivot table average totals_with nulls.jpg

      Qlik is adjusting the totals, so I don't think the Null warehouse is throwing this off.  Perhaps my formula needs some fine tuning?