How to write calculation: Rolling Headcount by Length of Service <=365 Days.

Scott_Turner_

I am trying to write a calculation to provide the Rolling Headcount at the end of the period for Team Members with Length of Service <= 365 days.

I have a validated calculation to calculate the headcount at the end of each month (Fig 1. NH - Headcount by LOS).

I have tried a few different ways to write the Rolling Headcount calculation utilising PERIODROLLING (Fig 2. TEST NH - Rolling 12 Month Headcount) however I do not seem to be able to get this correct. Fig 3 shows the table populating the Headcount at the end of each month and the incorrect rolling headcount data.

Any thoughts on the correct calculation for Rolling 12 Month Headcount?

Thanks Scott

JohnW-Oracle

Hi @Scott_Turner_ ,

It looks like this report has multiple grains. One option may be to add the first calculated column to your semantic model or add it to a data set. Then use that column in your PERIODROLLING calculation.

Our documentation regarding PERIODROLLING is here: https://docs.oracle.com/en/cloud/paas/analytics-cloud/acmdg/model-time-series-data.html#GUID-B7C1DB63-74C7-46A4-B4C1-A647453DA92B

Time series queries are valid only if the time series grain is at the query grain or higher. ** The PERIODROLLING function does not have a time series grain; instead, you specify a start and end period in the function.

In another post, you had mentioned Hierarchies, so make sure you are setting the chronological keys and mentioned here:

https://docs.oracle.com/en/cloud/paas/analytics-cloud/acmdg/model-time-series-data-semantic-model.html#GUID-C82DC188-544E-499D-B78F-3E69F9CFF2FF.

Regards,

John

Sumanth V -Oracle

@Scott_Turner_ - This thread appears to be a duplicate of the below community thread where you have already received a solution

https://community.oracle.com/products/oracleanalytics/discussion/21908/how-to-write-calculation-headcount-filtered-by-length-of-service

Kindly close this thread as a duplicate and please avoid posting duplicate threads to prevent ambiguity. Thank you for considering.

Scott_Turner_

Hi @Sumanth V -Oracle ,

This is a different question to the other thread, and I raised this question as advised by @JohnW-Oracle. The other tread is about calculating Headcount at a point in time and this thread is about calculating a rolling headcount.

Thanks

Scott