topic Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi in QlikView

Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on field

jmcdermott — Wed, 25 Nov 2020 16:16:04 GMT

Good afternoon All,

I am trying to get a Chart in Qlikview that look like this output below. I have attached the qvw with the data. I have a sorting field call "Sorting" that gets the data in the order that I want but I don't want the field to be displayed but want the sort to happen based on that field. Also I am wanting subtotals at "SL_Account_Group", when I do this under presentation tab and check show partial sums, it creates the subtotal after every SL_Bgt_Acct_No. You can see it in the qvw, can someone help me solve my issue?

Thanks,

Josh

611040	Salaries	59,950	69,160
611020	Overtime	2,500	2,500

	SUBTOTAL - Salaries	62,450	71,660

613010	Year End Bonus	0	0

	SUBTOTAL - Bonus	0	0

616010	401(K) Matching Contribution	2,518	2,905

	SUBTOTAL - Expenses on Allowance for Retirement	2,518	2,905

618010	Payroll Taxes	4,634	5,299

	SUBTOTAL - Legal Welfare	4,634	5,299
		-----------	-----------
	TOTAL - Salary & Related	69,602	79,864
	Avg Personel Exp Per Hdct	2,784	3,195

620030	Group Medical Premiums	20,704	20,704
620031	Group Dental Premiums	455	456
620082	Life & Disab Insurance	122	122
620084	Worker's Compensation	537	536
620090	Uniforms/Work Shoes/Glasses	400	400

	SUBTOTAL - Welfare Expense	22,217	22,217

Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi

Stoyan_Terziev — Wed, 13 Nov 2019 20:56:15 GMT

Hello Josh,

You need to remove the Sorting dimension from your chart.

You need to add the following sort order to the top Dimension in your dimensions list like this:

Then you need to find the field you want to add subtotals to (SL_Bgt_Acct_No I think it is in your case) and on Presentation -> check the Show Partial Sums for that field only like this:

You will get this result (qvw is attached):

I hope that helps!

Kind regards,

S.T.

Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi

jmcdermott — Wed, 13 Nov 2019 21:49:24 GMT

S.T.

Thanks it works as expected.

Re: Subtotals in Pivot table chart and hiding column from chart but maintaining the sort based on fi

jmcdermott — Fri, 15 Nov 2019 19:08:18 GMT

S.T or anyone else , I have a second table now that I want to combine current fiscal year actuals( 1st 7 fiscal periods) and remaining periods as bgt (periods 8-12). Also the totals and %'s and difference columns out to the right. What I want the output to look like is in the paste below. Attached is the qvw with the details. If anyone could help it would be much appreciated.

Thanks,

Josh

		Act	Act	Act	Act	Act	Act	Act	Bgt	Bgt	Bgt	Bgt	Bgt	Total Act&Fcst	Total Budget	% Act& Fcst /Budget	O/(U) Budget
Account	Description	Period 1	Period 2	Period 3	Period 4	Period 5	Period 6	Period 7	Period 8	Period 9	Period 10	Period 11	Period 12				Variance
		Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Total	Total

920000	Headcount	19.0	19.0	20.0	22.0	21.5	22.5	25.0	25.0	25.0	25.0	25.0	25.0	22.8	25.0	91%	(2.2)
930000	Contract Labor Headcount	0.9	0.1	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.1	0.0	0%	0.1

611040	Salaries	50,557	60,797	50,130	63,050	56,334	54,324	69,525	63,682	66,784	70,107	61,025	67,464	733,781	791,682	93%	($57,901)
611020	Overtime	3,255	5,815	565	662	307	820	2,500	2,500	2,500	2,500	2,500	2,500	26,424	30,000	88%	($3,576)

	SUBTOTAL - Salaries	53,813	66,612	50,695	63,712	56,642	55,144	72,025	66,182	69,284	72,607	63,525	69,964	760,205	821,682	93%	($61,477)

613010	Year End Bonus	0	0	0	0	0	0	0	0	17,500	0	0	0	17,500	17,500	100%	$0

	SUBTOTAL - Bonus	0	0	0	0	0	0	0	0	17,500	0	0	0	17,500	17,500	100%	$0

616010	401(K) Matching Contribution	2,255	2,348	2,010	2,055	3,158	2,086	2,920	2,675	2,805	2,945	2,563	2,833	30,653	33,251	92%	($2,598)

	SUBTOTAL - Expenses on Allowance for Retirement	2,255	2,348	2,010	2,055	3,158	2,086	2,920	2,675	2,805	2,945	2,563	2,833	30,653	33,251	92%	($2,598)

618010	Payroll Taxes	4,524	4,769	3,766	4,948	4,305	4,177	5,319	4,872	5,109	6,835	5,926	6,309	60,859	65,525	93%	($4,666)

	SUBTOTAL - Legal Welfare	4,524	4,769	3,766	4,948	4,305	4,177	5,319	4,872	5,109	6,835	5,926	6,309	60,859	65,525	93%	($4,666)
		-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------	-----------
	TOTAL - Salary & Related	60,591	73,729	56,471	70,715	64,105	61,407	80,264	73,728	94,698	82,387	72,014	79,107	869,217	937,957	93%	($68,740)
	Avg Personel Exp Per Hdct	3,039	3,862	2,824	3,214	2,982	2,729	3,211	2,949	3,788	3,295	2,881	3,164	37,925	37,518	101%	$407

620030	Group Medical Premiums	15,620	8,867	13,780	23,310	19,457	19,151	20,704	20,704	20,704	20,704	20,704	20,703	224,406	248,442	90%	($24,036)
620031	Group Dental Premiums	302	467	162	400	95	219	456	456	455	455	456	455	4,378	5,466	80%	($1,088)
620082	Life & Disab Insurance	394	132	1,166	282	-844	410	122	122	122	122	122	122	2,271	1,461	155%	$810
620084	Worker's Compensation	856	856	856	856	856	856	537	536	537	537	537	537	8,353	6,438	130%	$1,915
620090	Uniforms/Work Shoes/Glasses	0	0	0	0	0	0	400	400	400	400	400	400	2,400	4,800	50%	($2,400)
620091	Medical Transport	0	0	0	70	0	0	0	0	0	0	0	0	70	0	0%	$70

	SUBTOTAL - Welfare Expense	17,172	10,321	15,964	24,918	19,564	20,636	22,217	22,217	22,217	22,217	22,217	22,217	241,878	266,607	91%	($24,729)