How to Calculate Unduplicated FTE for each EMPLID in a Calendar Quarter in SAS Visual Analytics

[nonuoha]

Hi,

I am developing a dashboard that require total paid and unpaid FTE in a fiscal year or calendar year quarter.  My dataset contains a row for each active employee in a month, meaning that an employee who is active in all three months of a quarter have three rows in the dataset.  My calculation only need one active row in a quarter not three.

Here's a sample dataset of three employees (A, B, C) each with FTE of 1 in the first quarter of 2021

Jan - A = 1, B = 1, C = 1

Feb - A = 1, B = 1, C = 1

Mar - B = 1, C = 1

Expected total paid FTE for the three employees in first quarter is 3 and not 8.

If there's a document that will assist me on this, please direct me to it.

Thank You!

[Sam_SAS]

Hello,

If you duplicate the date column and apply a Quarter format, you could visualize the data that way. You could also use this Quarter column in a time hierarchy like Year>Quarter>Date that the user could drill into.

You can also use the Period() operator in a calculated item with an expression like:

Period(_Sum_, 'Product Sale'n, _IgnoreAllTimeFrameFilters_,
'Transaction Date'n, _ByQuarter_)

This calculates the sum value for each quarter.

Let us know if that helps.

Thanks,

Sam