Calculate 12 month average for specific point in time

[iczkla]

Hi All,

I am creating turnover dashboard where my main metric is attrition, which is calculated as number of workers who left organisation in specific period divided per average headcount for the same period. I want to create a table where I would be showing 3 ways to calculate attrition side by side tracked on monthly basis:

- monthly - # leavers/# monthly HC

- year to date - # of leavers within year/ average HC for year

- 12 rolling months - # of leavers within last 12 months/ average HC within last 12 months

The table should look like this:

The problem I have is that I am not able to create measures which would be flexible enough to display ytd or 12 rolling results for date shown on top of the table... This is [Report Period] field which is format date YYYY-MM, however I don't know how I should reference this within my measure.

Basic (monthly) calculation looks like this:

=count({<[Terminated]={'Yes'}>}[Employee ID])/count([Employee ID])

Any help on this will be greatly appreciated. Thank you!

[Clement15]

Hello, do you have a calendar in your application? If so you can try this:

- year to date 
count({<[Terminated]={'Yes'},Date={">=$(=Date(YearStart(max(Date)))) <=$(=Date(YearEnd(max(Date))))"}>}[Employee ID])/count([Employee ID])

- 12 rolling months

count({<[Terminated]={'Yes'},Date={">=$(=Date(addmonths(max(Date),-6))) <=$(=Date(addmonths(max(Date),6)))"}>}[Employee ID])/count([Employee ID])

I don't know if this will work but it should have the idea to help you

[iczkla]

Hi Clement,

Many thanks for your response.

Unfortunately this does not work as it refers to maximum date available within dataset, whilst it needs to refer to date listed in specific column in my table. So if I have column for January 2024 it should calculate 12 months back from this date, if December 2023 12 months back from this one and so on.

[ElisaF · Accepted solution]

Hello,

You can used the As-of-Table technical approach. See the below links:

https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130

https://www.quickintelligence.co.uk/compare-rolling-twelve-month-period-in-qlik/

Eliza

[iczkla]

Thank you, with help of articles you have provided I was able to successfully implement this solution into my dashboard!