Jump to content

Recommended Posts

Posted

I am working with a crosstable trying to achieve a forecast value that is a calculation of Month To Date admissions divided by todays date and then multiplied by the number of days in the month. I have the calculated columns as such:

MTD -case

when (Month([REG DATE])=Month(DateTimeNow())) and (Year([REG DATE])=2020) then [ENCOUNTER ID]

end

Todays date -Day(DateTimeNow())

Days inmonth -if((month([REG DATE])=10) or (month([REG DATE])=12) or (month([REG DATE])=8) or (month([REG DATE])=7) or (month([REG DATE])=5) or (month([REG DATE])=3),31,if(month([REG DATE])=2,28,30))

I created a new calculated column called "Forecasted Admissions" and used this formula:UniqueCount([MTD]) / [Todays date] * [Days in month]

When I tried to add it to the cross table as a cell value, it wants some sort of aggregate function placed on it but I just need the literal value returned. So I tried to take the formula above -UniqueCount([MTD]) / [Todays date] * [Days in month]- and put it directly into the Cell Values expression. It is saying the expression is invalid.

Can someone help me achieve this I really want it to be listed beside the other numbers in my screenshot as you see but is this possible

Sample dataset attached along with my table screenshot to help in aiding my explanation.

Thanks!

Posted

No, it is in the screenshot.  I've typed it below as well if that makes it easier.ENCOUNTER ID|REG DATE|UNIT|ENCOUNTER TYPE

12345|9/1/2020|ICU|Inpatient

12346|9/1/2020|ICU|Inpatient

12347|9/2/2020|ICU|Observation

12348|9/12/2020|ICU|Inpatient

12349|9/15/2020|ICU|Inpatient

12350|9/15/2020|ICU|Observation

Posted

Please create the Forecast column also in your data table. The forecast will be identicall for all rows in the current month. Therefore you can use min,max, avg, first,.... function as aggregation in your cross table.

 

If you want to calculate it directly in the cross table then you have to wrap every part in an aggregation function. And as stated before it does not matter which one you use. Here I use MAX:

Max([MTD]) * Max([Days of Current Months]) / Max([Current Day])

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...