Formula to Include Multiple Filters from Choice Fields
Ok, I have been stumbling a few days over this trying to get the right formula. What I am doing is creating a sub table to give me the values I need, so then I can draw those into a chart correctly. However, every field I am pulling from is a multiple choice field. Here is my scenario:
Overall I need to gather success rates (overall #'s) amongst "Risk Levels" . I have 4 Risk Levels: Low, Medium, Significant, and Extreme. For now, I just want to gather their overall success #'s amongst those 4 categories. However, the success field is multiple choice and has 3 choices that can be successful which are ID's 1, 4, and 6. The Risk Level field is also a multiple choice and their ID's are 1, 2, 3, and 4. Then I have to make sure I only select the closed cases which is a field called status and the choice ID for closed is 2. Alright, now since we have all the data...now for the formula suggestions. My Parent Table is 'Clients'. My fields are Status, Risk Level, and Termination Reason.
These are some of the formulas I have messed with to gather overall success for "Low Risk"
if select Clients where Status = 3 and 'Termination Reason' = 1 = 4 = 6 then
count(select Clients where 'Risk Level' = 1)
end
Copy
count(select Clients where 'Risk Level' = 1 and Status = 3 and
'Termination Reason' = 1 or = 4 or = 6)
Copy
The first one gives me a result of 1. The second one gives me a result of 18. The true answer is 23. So we know those formulas are whack.
However, this formula gives me the correct amount of clients who are both low risk and closed, which is 137. So the problem lies in the termination reason field. Any thoughts?
count(select Clients where Status = 3 and 'Risk Level' = 1)
Copy
13 replies
-
Try changing the select to
select Clients where Status = 3 and ('Termination Reason' = 1 or 'Termination Reason' = 4 or 'Termination Reason'= 6)and similarly with the second select statement
Regards
John
-
Well, that now gave me 44, lol for the second code and it gave me 153 for the first code. We can throw the first formula out the window, because all that is doing is just overall counting all Low Risk clients despite any conditions. I'm on track with the second formula, however need to set the condition correctly.
To recap: Count only the Low Risk (Risk Level =1) clients that are closed (Status =3) and have either of the 3 Termination Reasons selected (Termination Reason = 1 or 4 or 6)
-
To make things easier...we can even remove the status field from the entire field as termination reasons would only be selected if they were closed anyway.
-
I found a large part of the problem. Not sure why this is occurring. However, when I do a count of just only the successful selections for termination reasons, I get a result of 1. The answer should be 204.
count(Clients.'Termination Reason' = 1 and Clients.'Termination Reason' = 4 and Clients.'Termination Reason' = 6) -
Does this gives the correct result?:
count(Clients['Termination Reason' = 1]) + count(Clients ['Termination Reason' = 4]) + count(Clients ['Termination Reason' = 6])You can even expand to
count(Clients[Status = 3 and 'Termination Reason' = 1]) + count(Clients [Status = 3 and 'Termination Reason' = 4]) + count(Clients [Status = 3 and 'Termination Reason' = 6])Steven
-
No, if I put the brackets where you indicate I get errors that explain the expression does not return multiple values. If I put them on the outside of clients I get the total amount of fields...which is 3.
-
David Irving Since termination reason (and Risk Level) field is a multiple choice field records can have either 1, 4, or 6 plus any other selection 2,3,5. So doing a search Termination Reason = 1 tell Ninox to look for records that only have choice 1, not choices that contain 1. Sadly I don't think changing it to like 1 is the solution. I'm not near my computer with the app so I can't do any testing, but maybe later today.
You can look at this post to maybe get some ideas on how to refine the search.
-
With the way the choices are, a user can actually only select one (although its multiple choice in our environment it just does not make sense for the user to select more than one.
A simple formula like this does pull up the correct #. My problem is trying to combine this field as a condition.
count(select Clients where 'Termination Reason' = 1) -
And this formula just gave me the correct amount for Low Risk (Risk Level =1) clients who have Successfully been Sentenced (Termination Reason =1)
count(select Clients where 'Termination Reason' = 1 and 'Risk Level' = 1) -
So I did a long workaround solution. Not the easiest, but all I could do for now. I created another Table with a link and created a Form with all new fields pulling all the data I need. Then I'm able to make easier formulas and generate all the charts I want.
-
I forgot that multiple choice fields are numbered differently than choice fields. You would think that the number shown next to the text is the number you would reference. But it is not that easy.
Create a new formula field next to Termination Reason and put this in the formula:
number('Termination Reason')Now select choices and you will see that the 4th choice is not 4 but 8. The reason behind this is because you can select multiple items so Ninox has to create a way of keeping track with a single number. Make multiple selections and you can see how Ninox is trying to keep track.
Now change the above code to:
numbers('Termination Reason')And now you will see an array of choice numbers.
So now you can change your code to:
select Clients where Status = 2 and (numbers('Termination Reason') = 1 or numbers('Termination Reason') = 4 or numbers('Termination Reason') = 6)You said you never use the multiple choice function, so to make things easier I would recommend, if you can, change them to choice fields.
This only works if only one choice is selected. If you ever use the multiple choice feature then the code would have to change.
-
Ok, I figured out an easier solution when I stumbled upon getting a formula using sum for multiple fields that also included a choice field variable. I needed to be using brackets in the correct way.
count((select Clients)['Termination Reason' = 1 or 'Termination Reason' = 4 or 'Termination Reason' =6 ].'Status'=3)
Content aside
- Status Answered
- 2 yrs agoLast active
- 13Replies
- 448Views
-
4
Following