month on month trend reporting on headcount using stories

[arunsesh]

Has anyone created month on month trend reporting using stories reports on successfactors. I have been able to create year on year reporting but not month on month. the standard stories template also talks about only the year on year reporting.

[arunsesh]

Yes Jasper, please see below

1. INACTIVE_DATE: ADDDAYTODATE([Employment#Job Information#Actual Last Working day],1) :-This is to include all people who have left on 31st of the month as active for that month.
2. FDCY: TODATE(CONCAT(TOTEXT(YEAR(CURRENTDATE())), "/01/01"),"yyyy/mm/dd" ) :- This is to caclulate the first day of the current year. You will need to create a similar forumla for previous year or as many years you need historically as well
3. JAN_CY: IF((((( [User#Basic User Information#Hire Date]) <=ADDMONTHTODATE([FDCY],1))and( ISNULL([INACTIVE_DATE]))) OR((([INACTIVE_DATE])>=ADDMONTHTODATE([FDCY],1)))), 1, 0 ). :- this gives the JAN current year headcount
4. Similarly we can write formulae for each month of current year and past years.

[arunsesh]

thanks Jasper, i was able to do it but wasnt expecting it to be like this for a simple trend report.

[mohammadbuhidma65]

Hi Arun,

Do you still have the formula for the month on month trend?

[nlgro02343]

I did one with SOP, EOP, EOP-1 and current date (so as I'm even capable of doing it on day level, I doubt month is an issue). Keep in mind that each month will likely have to be a rather heavy formula to achieve this effect too on which you'd use a restricted measure in your story on the value of formula (i.e. Jan below, where Jan in this case is the month on which you calculate the headcount and ZZZZ is the respective january date). I reckon you'd need to polish it as I did this on day level, but it gives an idea of what kind of stuff you can do to get to the desired result.

IF([ZZZZ] > [Employment#Job Information#Termination Date] AND NOT(ISNULL( [Employment#Job Information#Termination Date] )),'X' ,IF([Employment#Job Information#Hire Date] >[ZZZZ],'X' , IF([ZZZZ]=[Employment#Job Information#Effective Date] ,'Jan' ,IF([Employment#Job Information#Effective Date] <=[ZZZZ] AND [Employment#Job Information#Effective End Date] >=[ZZZZ] ,'Jan' , '' ))) )