# 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
Did you mean:
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
MVP

May be this

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

Hi Sunny,

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

MVP

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

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

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

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!

MVP

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

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