SubTotals

[nvrphanikumar]

HI

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

[hector_munoz]

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

[nvrphanikumar]

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

[hector_munoz · Accepted solution]

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,

H

[nvrphanikumar]

Thank you!