Totals based on Min & Max

[Anonymous]

Hi

I have a table (below), and I want to calculate the turnover of staff using the Min & Max Month.  I have highlighted the data I need to show as a summary

Start_Month	Staff in post at start of Month	Staff in post at end of Month	Avg Month Staff Count	Sum(Leavers)
01/01/2015	18	19	18.5	1
01/02/2015	19	18	18.5	0
01/03/2015	18	19	18.5	0
01/04/2015	19	17	18	1
01/05/2015	17	17	17	0
01/06/2015	17	15	16	0
01/07/2015	15	17	16	0
01/08/2015	17	17	17	0
01/09/2015	18	20	19	0
01/10/2015	20	23	21.5	1
01/11/2015	22	21	21.5	0
01/12/2015	21	22	21.5	0

I want to show the following as a table.  How do I do it ?

Staff in post at start of Month	Staff in post at end of Month	Avg Month Staff Count	Min_Month	Max_Month	Leavers	Turnover (Leavers/Avg Month Staff Count)
18	22	20	01/01/2015	01/12/2015	3	15%

Any help appreciated?

Regards

Phil

[jonathandienst]

Do you have a field in your source for Leavers, or are you calculating it? How are  you setting min and max months?

[Anonymous]

Yes there is a field for leavers, and I have calculated the min / max by creating an expression min(Start_Month) / Max(Start_Month), then used the selection to determine start and end points.

Hope that makes sense

[Anonymous]

I mean (Max(End_Month)

[Anonymous · Accepted solution]

Hi

use following expression in the pivot table without any DIMENSION

Sum({$<[Start_Month]={"$(=Date((Min(Start_Month)), 'MM/DD/YYYY'))"}>} [Staff in post at start of Month]  )

Sum({$<[Start_Month]={"$(=Date((Max(Start_Month)), 'MM/DD/YYYY'))"}>}[Staff in post at end of Month]  )

=Date((Max(Start_Month)), 'MM/DD/YYYY')

=Date((Min(Start_Month)), 'MM/DD/YYYY')

sum([Sum(Leavers)])

and about this metric :

Avg Month Staff Count : 20

I am getting  as 18.8 ............please check and confirm the calculation for this field ?

[Anonymous]

Slight adjustment needed for UK dates, but worked well.

In regards to the Avg headcount.  The average in a total of start count (18) & end count (22) then divided by 2

18+22 = 40

40 / 2 = 20

[Anonymous]

thanks BTW