Calculating Percentage based on multiple dates; calculations based on date selection

user03841
user03841 Member
in Archive

I would like to the calculate the percentage of employees who participated in certain programs (ProgramEnrollDate and ProgramDisEnrollDate) while employed (HireDate and TerminationDate where TerminationDate may be null for currently employed employees) in self-sufficiency improvement programs based on a selectable reporting start and stop dates (i.e. using “Between” in DOMO to select the actively employed date range).

 

The participants may be enrolled during the entire period or just a portion of the selected date range. If they were enrolled in a program but were not employed by Goodwill during the program participation period, then their program participation will not be counted.

 

To select currently active employees, the TerminationDate field would be used.  How can employees active during another time period be selected?

For example, if 11/10/2017 was selected as the DOMO start date and 12/16/2017 as the DOMO end date, employees who were employed on or by 12/16/2017 or not terminated before 11/10/2017 would be selected.  Their employment dates would then be compared against their program enrollment and disenrollment dates to see if they were simultaneously employed and enrolled in the self-sufficiency programs.

 

Is there a variable or beast mode calculation for the DOMO date range or date filter begin and end dates?

Comments

  • laura_igl
    laura_igl Member

    Hi,

    Anyone can help out with this request?

     

    Thanks,

  • Chips
    Chips Domo Employee

    Seems like there may be a (complicated albeit) CASE logic statement to be created here. 

     

    Is your data structured in a such a way where all the fields you've outlined could be considered (could aggregate if needed) simultaneously?

     

    *Note have just typed this here in Dojo, haven't validated, but to illustrate my thought:

     

    COUNT(DISTINCT

    CASE

    WHEN TerminationDate IS NULL AND HireDate < ProgramDisEnrollDate THEN 'UNIQUE ID'

    WHEN TerminationDate IS NOT NULL AND HireDate < ProgramDisEnrollDate AND TerminationDate >ProgramEnrollDate THEN 'UNIQUE ID'

    ELSE 'UNIQUE ID'

    END)

    /

    COUNT(DISTINCT 

    CASE WHEN HIRE DATE IS NOT NULL THEN 'UNIQUE ID'

    ELSE ''

    END)

     

     

     

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • user03841
    user03841 Member

    Yes I could aggregate the data.  My biggest challenge is determining which employees are active during a selection period (i.e. start and stop dates) that can vary according to input that the card user selects (via DOMO Date Range Between fields).

  • Chips
    Chips Domo Employee

    Understood, and for that I am unaware of a beast mode solution. 

     

    I could envision a Magic ETL solution whereby you effectively create columns for each date and evaluate if someone was employed and if the program was active. 

     

    So in your example you would write columns to your dataset for 11/10, 11/11 etc and then do evaluations for each person and program. 

     

    Inelegant at best! But I can't see a BeastMode that would do it.

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"

Categories