Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to create an expression that would show the average for an entire month. Here is the set analysis that I currently have.
AVG({<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}AGGR(SUM({<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}[Actual Total Staffing]),[Shift Text],DATE))
It returns the following data:
Date | Average |
1/1/2020 | 260.6667 |
1/2/2020 | 268 |
1/3/2020 | 256.6667 |
1/4/2020 | 253.16667 |
1/5/2020 | 253.16667 |
1/6/2020 | 243.16667 |
1/7/2020 | 257.16667 |
1/8/2020 | 266.83333 |
1/9/2020 | 263.33333 |
The average for all days is 258.625.
I want the output to be the following:
1/1/2020 | 258.625 |
1/2/2020 | 258.625 |
1/3/2020 | 258.625 |
1/4/2020 | 258.625 |
1/5/2020 | 258.625 |
1/6/2020 | 258.625 |
1/7/2020 | 258.625 |
1/8/2020 | 258.625 |
1/9/2020 | 258.625 |
Thanks for any assistance.
The code is actually. I forgot the total word in the beginning. What is happening is that if I do not have any date selected it is bringing back all the dates since 2014 instead of the current month like I want it to.
AVG(total{<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}AGGR(SUM({<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}[Actual Total Staffing]),[Shift Text],DATE))
and if you test :
avg( total aggr(AVG(total {<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}AGGR(SUM({<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}[Actual Total Staffing]),[Shift Text],DATE)),Date))
That doesn't return anything.
avg( total aggr(AVG(total {<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}AGGR(SUM({<DateType={'STAFFING_CENSUS'}, PROVIDER_TYPE_ROLLUP = {'All RN'}, Year=, Month=, [Month-Yr]=, DATE={'>=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))'}>}[Actual Total Staffing]),[Shift Text],DATE)),DATE))
Date -> DATE
That still gives me all the days instead of just this month. I appreciate you help.