Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to calculate in a bar chart the implementation of scores with this methodology:
Dimension:
=if([Movement Type]='Termination Voluntary' or 'Termination Involuntary', 'Implemented', 'Not Implemented')
Measure:
Count(distinct{<[OPR 2018]={'1B'}>}"Global ID")
This works fine, however what i would like is, that once a person has an implemented status, I do not want the graph to calculate the Not implemented status.
For example as shown in the below graph, the bar represents only 1 person. This person has an implemented status and not implemented due to multiple Movement types. But as the person already has been implemented, I do not want the bar chart to show to not implemented count. Is this possible?
You can look at p() and e() here
P() & E() and where do you use them?
and also here
p() and e() function in Set Analysis #Part 30 (Qlikview Tutorial) - YouTube
Change your expression to this for selection in Name
Count(DISTINCT {<[OPR 2018] = {'1B'}, Name *= p({<[Movement Type] = {'Termination Voluntary', 'Termination Involuntary'}>})>} "Global ID")
Count(DISTINCT {<[OPR 2018] = {'1B'}, Name *= e({<[Movement Type] = {'Termination Voluntary', 'Termination Involuntary'}>})>} "Global ID")
Selection in Function should work without any issues... what happens when you select a function?
Are you using more than one dimension in this chart?
Yes, another dimension is Function.
So, for each Function... you want to see a single person in either Implemented or Not Implemented. If they are in Implemented, then they should not be counted in Not Implemented.
Correct
Name | Cycle | Score 2017 | Score 2018 | Movement | Implemented |
---|---|---|---|---|---|
Jack | 2018-2019 | 3A | 1B | Termination | Yes |
Jack | 2017-2018 | 3A | 1B | Promotion | No |
Jack | 2017-2018 | 3A | 1B | Promotion | No |
If this would be my data, i only want the chart to count the implemented move.
As the not implemented moves are not applicable after an implementation has been performed.
May be create this using 1 dimension and 2 expressions like this
Dimension
Function
Expression for Implemented
Count(DISTINCT {<[OPR 2018] = {'1B'}, Name = p({<[Movement Type] = {'Termination Voluntary', 'Termination Involuntary'}>})>} "Global ID")
Expression for not implemented
Count(DISTINCT {<[OPR 2018] = {'1B'}, Name = e({<[Movement Type] = {'Termination Voluntary', 'Termination Involuntary'}>})>} "Global ID")
Thanks,
It is partially working, the only issue now is that when I apply a filter this happens:
Unfiltered:
Filtered:
Where as before when I applied a filter it would only show the function associated with it.
So now when I filter for the function Sales, I only want to see sales in the graph, when I filter for a person that is implemented, I only want to see the implemented bar, not the not-implemented.
Is this possible?
Could you maybe also explain what the red "P" and "E" do? I am very curious what you did here.
Thanks a lot Sunny!
You can look at p() and e() here
P() & E() and where do you use them?
and also here
p() and e() function in Set Analysis #Part 30 (Qlikview Tutorial) - YouTube
Change your expression to this for selection in Name
Count(DISTINCT {<[OPR 2018] = {'1B'}, Name *= p({<[Movement Type] = {'Termination Voluntary', 'Termination Involuntary'}>})>} "Global ID")
Count(DISTINCT {<[OPR 2018] = {'1B'}, Name *= e({<[Movement Type] = {'Termination Voluntary', 'Termination Involuntary'}>})>} "Global ID")
Selection in Function should work without any issues... what happens when you select a function?
Amazing,
Thank you very much.
Hi Sunny,
One more question,
I have 1 field called cycle which represents the different cycles in which a movement has taken place. How should I adapt the code if I want the expression to look for a movement in the cycle '2018-2019'
The field "Cycle" containing the likes of;
2016-2017, 2017-2018, 2018-2019