help to calculate a expression

[sahilmahajan7]

Hi need some help

i want to calculate total no of hires :

suppose i select nov and 2017

i get the headcount for november and the attrition for november

total hires will be = headcount for (nov -1) - attrition for nov + x = headcount for nov

i want to find x please help me write it in a form of expression. will be a great help

[juraj_misina]

Hello Sahil,

you can achieve this by using set analysis (A Primer on Set Analysis). See a very simple example:

Sum(Headcount) //headcount for selected period
Sum({<Month={$(=Max(Month)-1)}>} Headcount) //headcount for previous month

This example does not account for change of year (when you want to calculate your measure for January), so it needs to be expanded, but you should get the idea.

Best

Juraj

[sahilmahajan7]

Hi Juraj thanks for the quick reply but still i am not able to get the desired result

i used this expression

count({<[As_On link.autoCalendar.Month]={$(=([As_On link.autoCalendar.Month])-1)}>} distinct EmployeeNo)

but i am not getting the result pls help

[juraj_misina]

Hi Sahil,

as I said, my example does not consider change in year, so hence the result. Try something like this:

count({<
    [As_On link.autoCalendar.Year]={$(=Year(AddMonths(MakeDate(Max([As_On link.autoCalendar.Year]), Max([As_On link.autoCalendar.Month])), -1)))},
    [As_On link.autoCalendar.Month]={$(=Month(AddMonths(MakeDate(Max([As_On link.autoCalendar.Year]), Max([As_On link.autoCalendar.Month])), -1)))}
>}
distinct EmployeeNo)

Again, this is just an example, although it should work in your case, but you should optimize it either by creating some sort of month sequence in your data (so you do not need to handle year when comparing Jan/Dec data across years), or by creating variables which would calculate correct year and month values for you, or by creating a The As-Of Table.

Hope this helps

Juraj