Count If... function help
Hi I need help on a formula for my database.. I have a field called Procedure, this field has several posible choices (different procedures), lets call them A,B,C,D,E,F.. I want to be able to count the number of individual choices ie how many A, B, C... there are. I am able to this part, but I don’t know how to restrict this to a date range. For example how to count how many A,B,C,D there where from Jan-Feb...
15 replies
-
for r in (select Table)[yearmonth(YourDateField) >="2019/01" and yearmonth(YourDateField) <="2019/02"]
"Put your count logic here"
end;
Disclaimer.. Code untested.. but hopefully you get the point.. Add criteria to your selection.
-
Thanks for the help.. is there a way to setup a prompt that asks for the date range that way I won’t have to edit formula each time I need to change date range
-
Sure.. just create a "dashboard" table.. and add the field(s) to that.. Then create a view object with the select statement.
-
This might be hard for a novice I have no idea how to get started : ( with a dashboard table... I sued the following to get the total number of each procedure : cnt(select Procedures where Procedure_Done like "AFIB") this gives me the total number of AFIB in the database. I guess with the above code i can narrow it ? how would i use the dashboard table ?
Sorry...
-
@ccarrieta .. I sent an email to your me.com with a sample database. Import that archive into your Ninox.
Enjoy.
-
Here is the procedures table..
Here is the Dashboard table.
Here is the code behind the count formula.
let t := this;
cnt((select Procedures)[text(Procedure_Done) like t.'Procedure Filter' and yearmonth(Date) >= t.From and yearmonth(Date) <= t.To]) -
Thanks!!! I love Ninox but the coding is not that straight forward... that is almost what i need.. I needed something like this but for several procedures at the same time.. I am able to do something similar in filemaker..
AFIB LOOP. PACER. TEE. DCCV date range 1/1/2019...2/31/2019
1/1/2019. 10. 20. 30. 15. 10
2/1/2019 2 12. 11. 4. 1
thanks for any help : )
-
One different approach is to create new table i.e. 'Procedures Report'. Create 2 date fields for your date range "From, To" and 5 formula fields with AFIB LOOP. PACER. TEE. DCCV.
Modifiyng @Mconneen's code you can have what you need.
Every record in this table will be a report for the date range.
Yes, Ninox is perfect for filtering ang grouping!
-
Something like this...
-
Thanks nick would it possible for you to send the database so I can import and learn your concept is exactly what i need i just don’t know how to edit Mconneen’s formula
-
-
This is exactly what I needed.. thanks you so much excellent learning case. Now i can use Ninox to track my procedures.. No more FileMaker.
-
.....except that I choose the procedure with a choice field ie a dropdown. When used this way it does not count the number of procedures..
-
sorry got it use the choice value...
-
Change the "Procedure Name" with the Number of the Value List:
e.g.
cnt(select Procedure where Procedure = 1 and Date >= t.From and Date <= t.To)
Content aside
- 5 yrs agoLast active
- 15Replies
- 4730Views