Casy Horsley Posted September 8, 2020 Share Posted September 8, 2020 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 More sharing options...
Fabian Duerr Posted September 8, 2020 Share Posted September 8, 2020 Do you want to display them together in one table Or shell the table display either one of them depending the user's choice Maybe you can share some sample data to get a better idea of the overall data structure. Link to comment Share on other sites More sharing options...
Casy Horsley Posted September 8, 2020 Author Share Posted September 8, 2020 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 More sharing options...
Fabian Duerr Posted September 9, 2020 Share Posted September 9, 2020 Alright, I understand the output (cross table) you wish for. But without knowing the data structure of the source data table for this cross table it's impossible to guide you to the right output. Please create a dummy data set (just the one source table for the cross tables) I can work with. Link to comment Share on other sites More sharing options...
Casy Horsley Posted September 9, 2020 Author Share Posted September 9, 2020 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 More sharing options...
Fabian Duerr Posted September 9, 2020 Share Posted September 9, 2020 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 More sharing options...
Fabian Duerr Posted September 9, 2020 Share Posted September 9, 2020 DXP with hard coded calculated columns Link to comment Share on other sites More sharing options...
Casy Horsley Posted September 9, 2020 Author Share Posted September 9, 2020 Are you able to save this as an older version Unfortunately I am unable to open. Link to comment Share on other sites More sharing options...
Fabian Duerr Posted September 9, 2020 Share Posted September 9, 2020 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 Link to comment Share on other sites More sharing options...
Casy Horsley Posted September 9, 2020 Author Share Posted September 9, 2020 Thank you! Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now