pivot table group

[ndeeleysww]

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
	Budget	Actual	£Var
	£	£	£
Excess mileage allowance	10	7	3
External Training	10	8	2
Gratuity	10	5	5
Long service awards	10	6	4
Occupational Health	10	7	3
Other allowances	10	5	5
Recruitment & relocation	10	8	2
Redundancy payments	10	10	0
Staff subsistence	10	14	(4)
Staff welfare	10	12	(2)
EMPLOYEE COSTS OTHER	100	82	18
			0
Callout - salaries	10	12	(2)
Employers NI contr salary	10	6	4
Employers pens contr sal.	10	5	5
Overtime - salaries	8	24	(16)
Salaries	10	12	(2)
Source Bonus Payments	9	12	(3)
Standby - salaries	10	14	(4)
SALARIES	67	85	(18)
Grand Total

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

Thank!

Neil

[Gysbert_Wassenaar · Accepted solution]

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:

ReportGroup	CostCategory
Excess mileage allowance	Excess mileage allowance
External Training	External Training
Gratuity	Gratuity
Long service awards	Long service awards
Occupational Health	Occupational Health
Other allowances	Other allowances
Recruitment & relocation	Recruitment & relocation
Redundancy payments	Redundancy payments
Staff subsistence	Staff subsistence
Staff welfare	Staff welfare
EMPLOYEE COSTS OTHER	Excess mileage allowance
EMPLOYEE COSTS OTHER	External Training
EMPLOYEE COSTS OTHER	Gratuity
EMPLOYEE COSTS OTHER	Long service awards
EMPLOYEE COSTS OTHER	Occupational Health
EMPLOYEE COSTS OTHER	Other allowances
EMPLOYEE COSTS OTHER	Recruitment & relocation
EMPLOYEE COSTS OTHER	Redundancy payments
EMPLOYEE COSTS OTHER	Staff subsistence
EMPLOYEE COSTS OTHER	Staff welfare
Callout - salaries	Callout - salaries
Employers NI contr salary	Employers NI contr salary
Employers pens contr sal.	Employers pens contr sal.
Overtime - salaries	Overtime - salaries
Salaries	Salaries
Source Bonus Payments	Source Bonus Payments
Standby - salaries	Standby - salaries
SALARIES	Callout - salaries
SALARIES	Employers NI contr salary
SALARIES	Employers pens contr sal.
SALARIES	Overtime - salaries
SALARIES	Salaries
SALARIES	Source Bonus Payments
SALARIES	Standby - salaries

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

[ndeeleysww]

Thanks Gysbert!