2 Replies Latest reply: May 4, 2018 11:00 AM by Bram Knuever RSS

    Pivot Table Help

    Ric Morgan

      Hi Guys,

       

      I'm currently creating a Pivot table to show the results of multiple KPI's broken down by month.

       

      My Rows are the KPI's themselves i.e.

       

      Ref           Name

      ---------------------------------------------------------------------------------

      A1            Number of Referrals

      A2            Number of Referrals seen within 5 working days

      A3            Number of Urgent Referrals seen within 2 working Days

       

      My Columns are the form submission months i.e.

       

      2017 Sept      2017 Oct       2017 Nov

       

      So I end up with the Pivot Table looking how I want it like this.....

       

      qs.png

       

      However, when I insert my formula for the Measures it does not work....

       

      I'm currently going with something like this....

       

      =if(Ref='A1',Count([Referral ID]),

      if(Ref='A2',Sum(Aggr(If(NetWorkDays("Submission date", "Start Time", '25/12/2017','26/12/2017','01/01/2018','01/01/2018') <= 6, 1, 0),[Referral ID])),

      if(Ref='A3',Sum(Aggr(If(NetWorkDays("Submission date", "Start Time", '25/12/2017','26/12/2017','01/01/2018','01/01/2018') <= 3, 1, 0),[Referral ID]))))

       

      And I'm either getting blanks or numbers that are completely off.

       

      I know these formulas on their own work as I've created them using the KPI chart in Qlik with the correct values. It appears to be an issue with the way I've set the pivot table up.

       

      Any help would be much appreciated