Help Returning The Value for the Latest Date with value

Josh_Godec123
Josh_Godec123 Member
in Beast Mode

Good afternoon,

My company reports a monthly census number for each of its Healthcare centers. I compare these numbers to the budget input. Majority of other metrics, like VISITS, NEW PATIENTS or DROPS can be summed for a year to date number, but Census should not be summed as it would report the latest total each month. I am currently using the formula in Beast Mode to calculate the prior month's census without having it summed up:

CASE

WHEN `HCC Census actual` and MONTH(`Date`) = MONTH(ADDDATE(CURDATE(), interval-1 Month)) AND

     YEAR(`Date`) = YEAR(ADDDATE(CURDATE(), interval-1 Month)) THEN (`HCC Census actual`)

Else 0

END

The issue with this is usually the prior months financials do not show until 10 days into the next month. For those 10 days prior to the number being reported, there will be no data in the prior month and the census will show as 0.

Need some advice on how to pull the latest census number when not 0 for those days in the month prior to the financials coming in.

Answers

  • Samuel.
    Samuel. Contributor
    edited February 2023

    Hi @Josh_Godec123

    If the data always uploads on the 10th day you can offset your current date function by 10 days and nest that as a replacement for curdate() 

    ADDDATE(CURDATE(), INTERVAL -10 DAY)).

    n.b. if it uploads at a certain time you can extend this process to include hours and minutes too.

    A more dynamic solution though if you are waiting on someone to send you data and it's approximately 10 days is to wrap your case statement with another case statement acting as a trigger based on the max date of your dataset.

    I.e. if the max date in your dataset is not in the last month then use a variation of your above case statement with ADDDATE(CURDATE(), INTERVAL -2 Month)). Once the data is uploaded your max date will be within the last month and that trigger case statement will swap back to your original code and go through the ADDDATE(CURDATE(), INTERVAL -1 Month)) process.

  • Josh_Godec123
    Josh_Godec123 Member

    Thank you Stuck. i like the idea of adding Case statement within a Case statement. Going to play with this logic. appreciate the reply

Categories