topic Average for month using Total in App Development

Average for month using Total

tchovanec — Wed, 22 Jan 2020 19:44:39 GMT

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.

Re: Average for month using Total

tchovanec — Wed, 22 Jan 2020 19:51:41 GMT

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

Re: Average for month using Total

Taoufiq_Zarra — Wed, 22 Jan 2020 19:58:20 GMT

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

Re: Average for month using Total

tchovanec — Wed, 22 Jan 2020 19:58:31 GMT

That doesn't return anything.

Re: Average for month using Total

Taoufiq_Zarra — Wed, 22 Jan 2020 19:59:57 GMT

Date -> DATE

Re: Average for month using Total

tchovanec — Wed, 22 Jan 2020 20:41:39 GMT

That still gives me all the days instead of just this month. I appreciate you help.