How to exclude some dimensions when doing a calculation

[#1]

I need to work out weighted headcount per area per month.

A simplified version of the current report looks something like this:

                                April  (and per each following month)                    

                                Weighted Headcount                     Number of Shifts             Number of Days

Area 1                                   10                                                           300                         30

Area2                                    40                                                           40                           1

Area3                                    2                                                              60                         30

Total                                    13.3                                                          400                       30 

But it should look like this:

April       (and per each following month the same principle)               

                                Weighted Headcount                     Number of Shifts             Number of Days

Area 1                                   10                                                           300                         30

Area2                                    1.3                                                          40                           30

Area3                                    2                                                              60                           30

Total                                      13.3                                                        400                         30

The formula should not use the actual number of days where there was activity in Area 2 (being 1), but it should use the actual number of days in the month (30) across all Areas.

Currently the formula used is sum(Shifts)/count(Distinct Days). The formula sum(Shifts)/count(Distinct TOTAL Days) would not work because I still need the days of each month to show seperately. How should the formula be changed in order to get the correct results?

Appreciate your help.

[agomes1971 · Accepted solution]

Hi,

try this

=Day(MonthEnd(Makedate(Year(Today()),MONTHFIELD)))

HTH

André Gomes

[effinty2112]

Hi Yessica,

                    This expression will return the number of days in a calendar month:

=Floor(MonthEnd(Month)) - Floor(MonthStart(Month)+1

To keep things tidy you could make a variable of this:

vNoOfDaysInMonth = Floor(MonthEnd(Month)) - Floor(MonthStart(Month)+1

Then try this for your weighted average:

Sum(Shifts)  / $(vNoOfDaysInMonth)

Good luck

Andrew

[#4]

Thank you very much!