Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
tchovanec
Creator II
Creator II

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:

DateAverage
1/1/2020260.6667
1/2/2020268
1/3/2020256.6667
1/4/2020253.16667
1/5/2020253.16667
1/6/2020243.16667
1/7/2020257.16667
1/8/2020266.83333
1/9/2020263.33333

 

The average for all days is 258.625. 

I want the output to be the following:

1/1/2020258.625
1/2/2020258.625
1/3/2020258.625
1/4/2020258.625
1/5/2020258.625
1/6/2020258.625
1/7/2020258.625
1/8/2020258.625
1/9/2020258.625
  

 

Thanks for any assistance. 

5 Replies
tchovanec
Creator II
Creator II
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))

 

Taoufiq_Zarra

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") 😉
tchovanec
Creator II
Creator II
Author

That doesn't return anything. 

Taoufiq_Zarra

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") 😉
tchovanec
Creator II
Creator II
Author

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