Custom expression / calculated column not producing expected results

[Casy Horsley]

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]

did you forget to attach the dataset

[Casy Horsley]

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]

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]

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])