Showing results for Yesterday, MTD and YTD

[Casy Horsley]

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

[Fabian Duerr]

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.

[Casy Horsley]

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.

[Fabian Duerr]

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.

[Casy Horsley]

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!

[Fabian Duerr]

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. 

[Fabian Duerr]

DXP with hard coded calculated columns

[Casy Horsley]

Are you able to save this as an older version  Unfortunately I am unable to open.  

[Fabian Duerr]

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

 

[Casy Horsley]

Thank you!