0

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

null
    • Mconneen
    • 5 yrs ago
    • Reported - view

    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.

    • ccarrieta
    • 5 yrs ago
    • Reported - view

    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 

    • Mconneen
    • 5 yrs ago
    • Reported - view

    Sure.. just create a "dashboard" table.. and add the field(s) to that.. Then create a view object with the select statement. 

    • ccarrieta
    • 5 yrs ago
    • Reported - view

    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...

    • Mconneen
    • 5 yrs ago
    • Reported - view

    @ccarrieta .. I sent an email to your me.com with a sample database. Import that archive into your Ninox.

    Enjoy. 

    • Mconneen
    • 5 yrs ago
    • Reported - view

    Here is the procedures table.. 

    ProceduresTable

    Here is the Dashboard table. 

    DashboardTable

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

    • ccarrieta
    • 5 yrs ago
    • Reported - view

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

    • Nick
    • 5 yrs ago
    • Reported - view

    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!

    • Nick
    • 5 yrs ago
    • Reported - view

    Something like this...

    Screen Shot 2019-02-24 at 12.13.32

    • ccarrieta
    • 5 yrs ago
    • Reported - view

    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 

    • Nick
    • 5 yrs ago
    • Reported - view
    • ccarrieta
    • 5 yrs ago
    • Reported - view

    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.

    • ccarrieta
    • 5 yrs ago
    • Reported - view

    .....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..

    • ccarrieta
    • 5 yrs ago
    • Reported - view

    sorry got it use the choice value...

    • Nick
    • 5 yrs ago
    • Reported - view

    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)

     

    Screen Shot 2019-02-24 at 22.05.34

Content aside

  • 5 yrs agoLast active
  • 15Replies
  • 4700Views