Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
leonchoo
Contributor
Contributor

Set Analysis to Ignore Dimension without using TOTAL

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?

 

8 Replies
sunny_talwar

May be this

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

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
Contributor II
Contributor II

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
Contributor
Contributor
Author

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
Contributor
Contributor
Author

Hi Sunny,

 

here's a dummy sample of the data:

MonthNumEmployeeNUMEmpStatusTraining TitleTraining HoursTraining Type
11activeA2Event
11activeB1Online
12activeB1Online
13activeA2Event
14activeA2Event
15activeA2Event
16activeB1Online
17activeB1Online
18activeA2Event
19activeB1Online
110activeA2Event
21inactive   
22activeC2Event
22activeD2Event
23inactive   
24activeC2Event
24activeD2Event
25activeC2Event
26activeD2Event
27activeD2Event
28activeD2Event
29activeD2Event
210activeD2Event
211activeD2Event

 

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

image.png

leonchoo
Contributor
Contributor
Author

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

EmployeeNUMEmpStatusTraining TitleTraining HoursTraining Type
201711activeA2Event
201711activeB1Online
201712activeB1Online
201713activeA2Event
201714activeA2Event
201715activeA2Event
201716activeB1Online
201717activeB1Online
201718activeA2Event
201719activeB1Online
2017110activeA2Event
201721inactive   
201722activeC2Event
201722activeD2Event
201723inactive   
201724activeC2Event
201724activeD2Event
201725activeC2Event
201726activeD2Event
201727activeD2Event
201728activeD2Event
201729activeD2Event
2017210activeD2Event
2017211activeD2Event
201811activeA2Event
201811inactive   
201812activeB1Online
201813inactive   
201814activeA2Event
201815activeA2Event
201816activeB1Online
201817activeB1Online
201818activeA2Event
201819activeB1Online
2018110activeA2Event
2018111active   
201821inactive   
201822activeC2Event
201822activeD2Event
201823inactive   
201824activeC2Event
201824activeD2Event
201825activeC2Event
201826activeD2Event
201827activeD2Event
201828activeD2Event
201829activeD2Event
2018210activeD2Event
2018211inactive