Cumulative Staff Turnover

[#1]

Hello

I am trying to create a cumulative turnover graph as hown below;

My calculation to get this is:

=num(sum({1<LeaversDate2={">=$(=addmonths(monthstart(min(MonthYear)),-11))"},LeaverFlag={'1'},Staff={'1'}>}LeaversFTE)/(sum([Person Headcount])),'##.##%')

This should provide me with a rolliing 12 month turnover however i only have the data relevant from April 209 as such the true cumulative figures dont come into effect until April 2010. i want to hide all the previous months but start at April 2010 if that is possible?

Any help would be greatly appreciated as boss need this asap and i am clutching at straws.

[brenner_martina]

Hi,

click on Full Accumulation left down in tab Expressions for your formula.

[#3]

Hello Martina

I currently have it set as Full Accumulation but what i require is only months from April 2010 to show in the graph, i need to load in the previous months to get the accumlation but unrue hwo to hide anything less than April 2010

[jonathandienst]

As far as I can recall, the accumulation options only accumulate what is shown on the graph. That means that if you filter the graph to only display from April 2010, it might still not give you what you need.

This means that you need to have an expression that sums data from the last 12 months. Then to prevent earlier than April 2010 displaying, have the expression return 0 or null when the date is earlier. Or else create a bookmark that filters out the values before April.

Jonathan

[#5]

Hello Jonthan

I tried creating if statement to lok at report date to see if greater than April 2010 which worked with regard to the x axis months however the figures are accumulating only from April 2010 when i need them to go back 12 months which is this case would actually be June 2009.

[johnw]

You can do it with a table that connects each AsOfMonthYear to twelve MonthYears with Rolling12Flag and to one MonthYear for CurrentMonthFlag. Use AsOfMonthYear as your dimension instead of MonthYear, and then use the flags to control how many months of data you include. For your case, probably something along these lines, though I've almost certainly got something wrong.

dimension = AsOfMonthYear
expression = num(sum({<Rolling12Flag={'1'},LeaverFlag={'1'},Staff={'1'}>}LeaversFTE)/(sum({<CurrentMonthFlag={'1'}>}[Person Headcount])),'##.##%')

This isn't your data, but attached is an example of the same technique (and a couple others) being applied to get a rolling 3 months and YTD.