# App Development

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for
Did you mean:  Creator

## Average for month using Total

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.

5 Replies  Creator
Author

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))``  Master II

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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉  Creator
Author

That doesn't return anything.  Master II
``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

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉  Creator
Author

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