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(Match(Warehouse,'HUR','SAL','SSS','SBF','YUM','YBF','YSS'),Warehouse,Null())

       

      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?