Custom expression / calculated column not producing expected results

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 in month - 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!

 

Attachments

AttachmentSize
Image icon capture.png25.71 KB

3 Comments

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?

 

Gaia Paolini - Sep 17, 2020 - 6:59am

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

casy.horsley - Sep 17, 2020 - 6:07am

did you forget to attach the dataset?

Gaia Paolini - Sep 17, 2020 - 2:38am
View More Comments + Add a Comment

(1) Answer

Login