Set Analysis to Ignore Dimension without using TOTAL

[leonchoo]

Hi there,

I currently have a formula : 

sum(TrainingHours)/(count($<MonthNum{"$(=Max({$}MonthNum))"}, EmpStatus = {'active'}>} distinct (EmployeeNum))

For which, I am counting average training hours for active employees based on the latest month.

I have put it in a chart with a dimension called Training Type where it consists of Event, Online, Test.

When I plucked the values in, I realised that the number of active employees changed due to the dimension's filter. 

Is there anyway for me to fix the number of active employees (the denominator) and disregard the dimension's filter? I have tried distinct TOTAL EmployeeNum but it gives me the total overall, not the active number of employees based on the latest month. 

Could someone advise me on how I can specifically disregard the dimension in my set analysis?

[sunny_talwar]

May be this

Sum(TrainingHours)/
Count($<MonthNum = {"$(=Max({$}MonthNum))"}, EmpStatus = {'active'}>} TOTAL <MonthNum> DISTINCT EmployeeNum)

[leonchoo]

Hi Sunny,

Thanks for your swift response! However, I have tried that method and it doesn't work.

[sunny_talwar]

Would you be able to share a sample where we can see the issue? Also, provide the expected output you would want to see based on the sample shared

[sstroop]

To disregard a specific dimension in set analysis, include the dimension 'Training Type' with = in the set analysis equation:

sum({<[Training Type]=>}TrainingHours)/(count($<MonthNum{"$(=Max({$}MonthNum))"}, EmpStatus = {'active'},[Training Type]=>} distinct (EmployeeNum))

[leonchoo]

Hi Sstroop,

I've tried it as well but it doesn't work since my Training Type is a dimension. I believe the code only works when I toggle it in the filter

[leonchoo]

Hi Sunny,

here's a dummy sample of the data:

MonthNum	EmployeeNUM	EmpStatus	Training Title	Training Hours	Training Type
1	1	active	A	2	Event
1	1	active	B	1	Online
1	2	active	B	1	Online
1	3	active	A	2	Event
1	4	active	A	2	Event
1	5	active	A	2	Event
1	6	active	B	1	Online
1	7	active	B	1	Online
1	8	active	A	2	Event
1	9	active	B	1	Online
1	10	active	A	2	Event
2	1	inactive
2	2	active	C	2	Event
2	2	active	D	2	Event
2	3	inactive
2	4	active	C	2	Event
2	4	active	D	2	Event
2	5	active	C	2	Event
2	6	active	D	2	Event
2	7	active	D	2	Event
2	8	active	D	2	Event
2	9	active	D	2	Event
2	10	active	D	2	Event
2	11	active	D	2	Event

Average Training Hours by End Jan = 17/10 where I have 10 active members

Average Training Hours by End Feb = 17+22/9 where I have 9 active members

Now, I have a bar chart where the dimension is the Training Type, and I want to find out the average training hours for the different types of training( Event, Online).

The number that I'd like to get is:

Average Training Hours by End Feb for Event = 34/9

Average Training Hours by End Feb for Online = 5/9

where I want to fix the number of active employees (I don't want the number of employees to be filtered by the training type)

Thanks!

[sunny_talwar]

In that case your intitial expression with TOTAL seems to be working for me

[leonchoo]

Hi Sunny,

In more detail, my dimensions are Training Type & Year (2017, 2018). There are 12 mths in total for each year and I used MonthNum={"$(=Max({$}MonthNum))"}  to obtain the latest month.

Could this be the reason why it does not work for my case? Could you advise?

Year	MonthNum	EmployeeNUM	EmpStatus	Training Title	Training Hours	Training Type
2017	1	1	active	A	2	Event
2017	1	1	active	B	1	Online
2017	1	2	active	B	1	Online
2017	1	3	active	A	2	Event
2017	1	4	active	A	2	Event
2017	1	5	active	A	2	Event
2017	1	6	active	B	1	Online
2017	1	7	active	B	1	Online
2017	1	8	active	A	2	Event
2017	1	9	active	B	1	Online
2017	1	10	active	A	2	Event
2017	2	1	inactive
2017	2	2	active	C	2	Event
2017	2	2	active	D	2	Event
2017	2	3	inactive
2017	2	4	active	C	2	Event
2017	2	4	active	D	2	Event
2017	2	5	active	C	2	Event
2017	2	6	active	D	2	Event
2017	2	7	active	D	2	Event
2017	2	8	active	D	2	Event
2017	2	9	active	D	2	Event
2017	2	10	active	D	2	Event
2017	2	11	active	D	2	Event
2018	1	1	active	A	2	Event
2018	1	1	inactive
2018	1	2	active	B	1	Online
2018	1	3	inactive
2018	1	4	active	A	2	Event
2018	1	5	active	A	2	Event
2018	1	6	active	B	1	Online
2018	1	7	active	B	1	Online
2018	1	8	active	A	2	Event
2018	1	9	active	B	1	Online
2018	1	10	active	A	2	Event
2018	1	11	active
2018	2	1	inactive
2018	2	2	active	C	2	Event
2018	2	2	active	D	2	Event
2018	2	3	inactive
2018	2	4	active	C	2	Event
2018	2	4	active	D	2	Event
2018	2	5	active	C	2	Event
2018	2	6	active	D	2	Event
2018	2	7	active	D	2	Event
2018	2	8	active	D	2	Event
2018	2	9	active	D	2	Event
2018	2	10	active	D	2	Event
2018	2	11	inactive