Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 ?
Do you have a field in your source for Leavers, or are you calculating it? How are you setting min and max months?
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
I mean (Max(End_Month)
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 ?
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
thanks BTW