Casy Horsley Posted September 15, 2020 Posted September 15, 2020 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!
Gaia Paolini Posted September 17, 2020 Posted September 17, 2020 did you forget to attach the dataset
Casy Horsley Posted September 17, 2020 Author Posted September 17, 2020 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
Gaia Paolini Posted September 17, 2020 Posted September 17, 2020 I am not clear on how you want to set up this cross table, but can you simply try to put Average(..) around your value and see if it works
Fabian Duerr Posted September 20, 2020 Posted September 20, 2020 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])
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