How to exclude some dimensions when doing a calculation

Thu, 25 Aug 2016 08:28:23 GMT

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.

Re: How to exclude some dimensions when doing a calculation

agomes1971 — Thu, 25 Aug 2016 09:21:51 GMT

Hi,

try this

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

HTH

André Gomes

Re: How to exclude some dimensions when doing a calculation

effinty2112 — Thu, 25 Aug 2016 09:39:58 GMT

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

Re: How to exclude some dimensions when doing a calculation

Thu, 25 Aug 2016 10:10:50 GMT

Thank you very much!

topic Re: How to exclude some dimensions when doing a calculation in QlikView

How to exclude some dimensions when doing a calculation

Re: How to exclude some dimensions when doing a calculation

Re: How to exclude some dimensions when doing a calculation

Re: How to exclude some dimensions when doing a calculation