topic Re: Set Analysis to Ignore Dimension without using TOTAL in QlikView

Set Analysis to Ignore Dimension without using TOTAL

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

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?

Re: Set Analysis to Ignore Dimension without using TOTAL

sunny_talwar — Wed, 23 Jan 2019 14:28:08 GMT

May be this

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

Re: Set Analysis to Ignore Dimension without using TOTAL

leonchoo — Wed, 23 Jan 2019 14:57:48 GMT

Hi Sunny,

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

Re: Set Analysis to Ignore Dimension without using TOTAL

sunny_talwar — Wed, 23 Jan 2019 16:05:12 GMT

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

Re: Set Analysis to Ignore Dimension without using TOTAL

sstroop — Wed, 23 Jan 2019 20:04:10 GMT

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))

Re: Set Analysis to Ignore Dimension without using TOTAL

leonchoo — Thu, 24 Jan 2019 01:20:04 GMT

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

Re: Set Analysis to Ignore Dimension without using TOTAL

leonchoo — Thu, 24 Jan 2019 01:35:43 GMT

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!

Re: Set Analysis to Ignore Dimension without using TOTAL

sunny_talwar — Thu, 24 Jan 2019 12:06:28 GMT

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

Re: Set Analysis to Ignore Dimension without using TOTAL

leonchoo — Mon, 28 Jan 2019 08:54:20 GMT

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