Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Edvin
Creator
Creator

Trying to count by excluding if a value exists

Hello guys (and girls).

I am trying to calculate for each USER or in this case OWNER, how much has he got on plate, his backlog.

In other words, I want to calculate ID's where the Process is NOT DONE or either no response from the company LD as in the last example. The process goes like this: it has to be SENT over to someone, and either it is approved DONE, or sent it back NOT DONE. It can be SENT multiple times, until the process is DONE. So the goal is to calculate which ID is not processed fully. I attached the example excel file. It has to be done in the front-end.

My expression looked like this:

count({<Process={'NOT DONE'}, Process-={'DONE'}>} distinct [ID])

But it doesn't show correctly, tried to do same thing with IF statement, tried to input aggr()... but no result.

Thanks to all of you in advance!

1 Solution

Accepted Solutions
Kushal_Chawda

@Edvin  try below

=count({<ID=e({<PROCESS={'DONE'}>})>} distinct [ID])

View solution in original post

9 Replies
Kushal_Chawda

@Edvin  try below

=count({<ID=e({<PROCESS={'DONE'}>})>} distinct [ID])

avinashelite

or may be

=count({<PROCESS-={'DONE'}>} distinct [ID])

Kushal_Chawda

@avinashelite  Your expression will count those IDs as well where Process is Done because one ID has multiple process associated. Which is not the requirement here

Edvin
Creator
Creator
Author

@avinashelite  yep, as Kush stated above, it does count more.

@Kushal_Chawda  It works! But I can't seem to implement more, what if there is one more process which I need to exclude?

Because, adding just like that:

=count({<ID=e({<PROCESS={'DONE'}, PROCESS={'NOT DONE_WITH CONDITION'}>})>} distinct [ID])

Doesn't work.

 

Kushal_Chawda

@Edvin  Not sure I am following. Can you share example with expected output? Meanwhile try below

count({<ID=e({<PROCESS={'DONE','NOT_DONE'}>})>} distinct [ID])

Edvin
Creator
Creator
Author

As I understand, the e() function excludes rows with a specific value?

So what have you given me, is that it count ID's where there is no DONE value for process, grouped by ID.

But what if I need to count ID's where there is no DONE value for process and NOT DONE_WITH CONDITION value for process, grouped by ID. I hope you did understand, otherwise, i'll attach an excel file

Kushal_Chawda

@Edvin  what is the condition?

Edvin
Creator
Creator
Author

@Kushal_Chawda  A little bit of misunderstanding, NOT DONE_WITH CONDITION is a value.

Process has 4 possible values = 'SENT','DONE','NOT DONE','NOT DONE_WITH CONDITION'.

I want to count ID's that never had Process = 'DONE' and 'NOT DONE_WITH CONDITION'

I tried your last expression, but it seems, that it only excludes 'NOT DONE_WITH CONDITION'

count({<ID=e({<PROCESS={'DONE','NOT DONE_WITH CONDITION'}>})>} distinct [ID])

Kushal_Chawda

@Edvin  Ideally this expression should work. Can you share sample with expected output?