Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ndeeleysww
Contributor

pivot table group


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

Tags (2)
1 Solution

Accepted Solutions

Re: pivot table group

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).


talk is cheap, supply exceeds demand
2 Replies

Re: pivot table group

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).


talk is cheap, supply exceeds demand
ndeeleysww
Contributor

Re: pivot table group

Thanks Gysbert!

Community Browser