Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

IF (True) Do not count x

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?

Not Implemented 1B.PNG

1 Solution

Accepted Solutions
Highlighted

You can look at p() and e() here

P() &amp; 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?

View solution in original post

10 Replies
Highlighted

Are you using more than one dimension in this chart?

Highlighted
Creator
Creator

Yes, another dimension is Function.

Highlighted

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.

Highlighted
Creator
Creator

Correct

NameCycleScore 2017Score 2018MovementImplemented
Jack2018-20193A1BTerminationYes
Jack2017-20183A1BPromotionNo
Jack2017-20183A1BPromotionNo

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.

Highlighted

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

Highlighted
Creator
Creator

Thanks,

It is partially working, the only issue now is that when I apply a filter this happens:

Unfiltered:

Unfiltered New.PNG

Filtered:

Filtered new.PNG

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!

Highlighted

You can look at p() and e() here

P() &amp; 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?

View solution in original post

Highlighted
Creator
Creator

Amazing,

Thank you very much.

Highlighted
Creator
Creator

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