Expression to calculate employee head count with previous year data

[Rajeshhyd]

Hi All,

I written a  filter which was build with 2 fields Month and year by writing expression month & Year . Once I select January 2019 in the filter my employee count should show employee in January 2019 -January 2018.

What will be the expression in this as we have 2 field joining in filter.

Your support is really appreciated

Thanks

[anthonyj · Accepted solution]

Hi @Rajeshhyd ,

If you're able to, I found the easiest way to do a comparison of a month and year with the same time last year is to create a column that is both "Month" and "Year" in your load script.
Then you're able to use the max( )" and "MonthStart( )" function to select the same period from last year.

For example:
If you created a column called "Month_Year" this will give you a count of employees for the period 12 months ago from the "Month_Year" selected.

count({<Month_Year={"$(=monthstart(max(Month_Year),-12))"}>}Employees)

As of today this would resolve to a count of employees that have a Month_Year date of '01/10/2020'.  You can then use this in a calculation if you need to do a comparison with current year.

For example:

count(Employees)
-
count({<Month_Year={"$(=monthstart(max(Month_Year),-12))"}>}Employees)

to assist with clarity you can add the function into your title so the user knows which period the comparison is for.

'Employees for ' & monthstart(max(Month_Year),-12)

This will show "Employees for 01/10/2020" on the label.

I hope I've understood your query correctly.

Thanks
Anthony