topic Re: SubTotals in QlikView

SubTotals

nvrphanikumar — Tue, 28 Mar 2017 22:47:08 GMT

Lets say, You want to show % of Male and Female for each Month like below.

# = count(EmployeeID)

Subtotal = count( total <Month> EmployeeID)

% = # / Subtotal for each Month.

	Male			Female
	#	Subtotal	%	#	Subtotal	%
Jan	5	11	45.45%	6	11	54.55%
Feb	3	10	30.00%	7	10	70.00%
March	4	9	44.44%	5	9	55.56%
April	5	11	45.45%	6	11	54.55%
May	6	13	46.15%	7	13	53.85%
June	7	15	46.67%	8	15	53.33%
July	8	17	47.06%	9	17	52.94%
August	9	19	47.37%	10	19	52.63%
September	10	21	47.62%	11	21	52.38%
October	11	23	47.83%	12	23	52.17%
November	12	25	48.00%	13	25	52.00%
December	13	27	48.15%	14	27	51.85%

These formula's mentioned above work fine with right numbers.

Lets say in the above example ,The user asked to limit the results by Default to Jan and Feb

My row expression is :

if(match(Month, 'Jan','Feb'),Month) .

and I countinue to use existing calculations, the results doesn't show right.They appear as below. You will notice Subtotal brings total for table mentioned above for all months instead of subtotal for Jan and Feb as It used to in above example.

# = count(EmployeeID)

Subtotal = count( total <Month> EmployeeID)

% = # / Subtotal for each Month.

	Male			Female
	#	Subtotal	%	#	Subtotal	%	SubTotal
Jan	5	201	2.49	6	201	2.99	11
Feb	3	201	1.49	7	201	3.48	10

Re: SubTotals

hector_munoz — Wed, 29 Mar 2017 01:21:02 GMT

Hi Phani,

Use set analysis in expressions instead of calculated dimensions:

# = Sum({<Month={Jan,Feb}>} Employees)

Subtotal = Sum({<Month={Jan,Feb}>} TOTAL <Month> Employees)

% = Sum({<Month={Jan,Feb}>} Employees) / Sum({<Month={Jan,Feb}>} TOTAL <Month> Employees)

I attach you a sample with data similar to yours.

Regards,H

Re: SubTotals

nvrphanikumar — Wed, 29 Mar 2017 01:44:27 GMT

HI Hector,

Thanks for your response,I tried to replicate it and Subtotal is still showing full total instead of total for each month.

Have you limited the rows to if(match(Month, 'Jan','Feb'),Month) ?

Sorry,I'm using qlik sense and couldn't open qvw file.

Thank you

Re: SubTotals

hector_munoz — Wed, 29 Mar 2017 01:49:06 GMT

OK, try to not use calculated dimension just Month, and use the following expresions instead yours:

# = Sum({<Month={Jan,Feb}>} Employees)

Subtotal = Sum({<Month={Jan,Feb}>} TOTAL <Month> Employees)

% = Sum({<Month={Jan,Feb}>} Employees) / Sum({<Month={Jan,Feb}>} TOTAL <Month> Employees)

Regards,

Re: SubTotals

nvrphanikumar — Wed, 29 Mar 2017 01:54:11 GMT

Thank you!