Jump to content
We've recently updated our Privacy Statement, available here. ×

Showing results for Yesterday, MTD and YTD


Casy Horsley

Recommended Posts

I have a cross table that I need to display admissions for "Yesterday", "MTD" and "YTD". These are broken down by type and location. I've tried creating a calculated column but the formula is either incorrect or that is not the approach I should be taking. What would be recommended to display these values in columns of a cross table

Example formula for MTD:CASE WHEN [Reg_Date]>=Date(Year(DateTimeNow()),Month(DateTimeNow()),1) THEN UniqueCount([ENCOUNTER ID]) ELSE NULL END

Link to comment
Share on other sites

I am currently trying to get it into one table but I am not set on that. Trying to figure out the best way to display it and getting the columns is my first step. Attached is a sample of how the data is currently reported. I have several data tables linked up in Tibco and they contain a years' worth of data.
Link to comment
Share on other sites

         
Facility  Encounter ID Reg Date Unit Type
A 12359 01/05/2020 GMS Inpatient
A 12360 02/05/2020 GMS Inpatient
A 12361 04/05/2020 GMS Observation
A 12362 09/07/2020 ICU Inpatient
A 12363 09/08/2020 ICU Inpatient

 

 

 

With the given table, the values I'd expect to return for reporting of unique encounters registered per unit are:

 

GMS - Yesterday (0), MTD (0), YTD (3)

 

ICU - Yesterday (1), MTD (2), YTD (2)

 

Thank you!

Link to comment
Share on other sites

Thank you for the table.

 

I would create three calculated columns: Yesterday, MTD, YTD.

 

In each column check if the 'reg date' is within a given time window. And if yes then make the value 1 else 0. There are multiple datetime function that will help you like Day, Month, Year or DateDiff.

 

Then use those three columns in your cross table. 

 

I think this is straight forward. But let me know if you need further support here. 

Link to comment
Share on other sites

Here are the three calculations for the three columns

 

case  
when DateDiff("day",[Reg Date],Date(DateTimeNow()))<=1 then 1 else 0
end

 

 

 

case  
when (Month([Reg Date])=Month(DateTimeNow())) and (Year([Reg Date])=2020) then 1 else 0
end

 

 

 

case  
when Year([Reg Date])= Year(DateTimeNow()) then 1 else 0
end

 

yesterday.png

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...