2 Replies Latest reply: Apr 22, 2015 9:19 AM by Neil Deeley RSS

    pivot table group

    Neil Deeley


      Hi all,

       

      Just to confirm that I am not mad - there isn't any grouping of data into the Sense Pivot Tables?

       

      I'm trying to replicate an excel spreadsheet:

       

      March
        2014/15
      Periodic
      BudgetActual£Var
      £££
      Excess mileage allowance1073
      External Training1082
      Gratuity1055
      Long service awards1064
      Occupational Health1073
      Other allowances1055
      Recruitment & relocation1082
      Redundancy payments10100
      Staff subsistence1014(4)
      Staff welfare1012(2)
      EMPLOYEE COSTS OTHER1008218
      0
      Callout - salaries1012(2)
      Employers NI contr salary1064
      Employers pens contr sal.1055
      Overtime - salaries824(16)
      Salaries1012(2)
      Source Bonus Payments912(3)
      Standby - salaries1014(4)
      SALARIES6785(18)
      Grand Total

      ...and am finding no method to currently achieve it in Sense.

       

      Thank!

      Neil

        • Re: pivot table group
          Gysbert Wassenaar

          No, Qlik Sense is not a spreadsheet application. You can't add arbitrary references to other pivot table cells. You can create an extra field in the load script that links cost categories with the group names. You'd create an extra table that looks like:

           

          ReportGroupCostCategory
          Excess mileage allowanceExcess mileage allowance
          External TrainingExternal Training
          GratuityGratuity
          Long service awardsLong service awards
          Occupational HealthOccupational Health
          Other allowancesOther allowances
          Recruitment & relocationRecruitment & relocation
          Redundancy paymentsRedundancy payments
          Staff subsistenceStaff subsistence
          Staff welfareStaff welfare
          EMPLOYEE COSTS OTHERExcess mileage allowance
          EMPLOYEE COSTS OTHERExternal Training
          EMPLOYEE COSTS OTHERGratuity
          EMPLOYEE COSTS OTHERLong service awards
          EMPLOYEE COSTS OTHEROccupational Health
          EMPLOYEE COSTS OTHEROther allowances
          EMPLOYEE COSTS OTHERRecruitment & relocation
          EMPLOYEE COSTS OTHERRedundancy payments
          EMPLOYEE COSTS OTHERStaff subsistence
          EMPLOYEE COSTS OTHERStaff welfare
          Callout - salariesCallout - salaries
          Employers NI contr salaryEmployers NI contr salary
          Employers pens contr sal.Employers pens contr sal.
          Overtime - salariesOvertime - salaries
          SalariesSalaries
          Source Bonus PaymentsSource Bonus Payments
          Standby - salariesStandby - salaries
          SALARIESCallout - salaries
          SALARIESEmployers NI contr salary
          SALARIESEmployers pens contr sal.
          SALARIESOvertime - salaries
          SALARIESSalaries
          SALARIESSource Bonus Payments
          SALARIESStandby - salaries

           

          You can then use the ReportGroup field as dimension instead of the original CostCategory (or whatever its actual name is).