YTD Calculation for headcount/FTEs

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

  • YTD Calculation for headcount/FTEs

    Hello all / Hallo ihr alle,

    I have the impression that this question was asked before, but the answers I found do not seem to fit my problem completely

    Let's consider the following cube: A

    Period: Jan, Feb, Mar....Dec, YTDJan, YTDFeb, YTDMar....YTD Dec, FY

    View of the data: YTD or Periodic

    Measures: different measures related to FTE (headcounts) and some not related to FTE (€, days etc)

    Using a rule, I would like to calculate a YTD for the measures that have FTE as an attribute and the once that have an attribute different than FTE I would like to leave them as they are.

    An example:

    'Total FTE' has FTE as an attribute:

    Period 1 | Period 2 | Period 3 | Period 4 | Period 5 | Period 6

    100 110 100 110 130 125

    Those are Periodic values. That means that Period 6 / YTD / Total FTE would normally be = 675

    BUT since this makes no sense and i always wanna have the lates period as the YTD value I need help with the rule

    In gereal no problem. I just created 12 rules that calculate the YTD for each Period:

    ['YTD','Jan'] = ['Periodic','Jan'] => for January YTD = Periodic

    But now also the elements which I do not want to have calculated this way and which do not have FTE as an attribute, are getting calculated this way. I have no idea how to exclude them from this rule and my problem is also that I'm not very experienced with rules...

    Does anyone have an idea?

    Thanks in advance ! / Vielen Dank vorab!

  • From what I understand you want to exclude Measures having attributes as FTE from YTD View,
    you can use STET() function to exclude them based of IF criteria, I have extended it to show you can calculate the YTD view in one calculation instead of having 12
    calculations, 1st type of calculation assumes that you have defined attribute 'Description' for Month dimension, and Attribute as 'Attribute' for Measure dimension

    ['YTD'] = B: IF(PALO.DATA("DatabaseName","#Measures",!'Measures',"Attribute")="FTE",STET(),
    IF(PALO.DATA("DatabaseName","#Period",!'Month','Description') == "January",
    PALO.DATA("DatabaseName","CubeName",!'Dim1'.... !'DimN-2',"Jan", "Periodic"),
    IF(PALO.DATA("DatabaseName","#Period",!'Month','Description') == "Febuary",
    PALO.DATA("DatabaseName","CubeName",!'Dim1'.... !'DimN-2',"Jan", "Periodic") + PALO.DATA("DatabaseName","CubeName",!'Dim1'.... !'DimN-2',"Feb", "Periodic")
    .. similarly do it for rest of the months)))

    The second one is more concise, but it assumes that you still have Month dimension with alternate consolidation paths for YTD, like Mar_YTD consolidates Jan, Feb & Mar elements, but have also view dimension having Periodic & YTD elements - This calculation would be faster but bit messy so could Hide the Month elements from the user.

    ['YTD'] = B: IF(PALO.DATA("DatabaseName","#Measures",!'Measures',"Attribute")="FTE",STET(),
    PALO.DATA("DatabaseName","CubeName",!'Dim1'.... !'DimN-2',CONCATENATE(!'Month',"_YTD"),"Periodic"))

    If you just want Periodic value instead of STET() for YTD, you can replace the STET() function with
    PALO.DATA("DatabaseName","CubeName",!'Dim1'.... !'DimN-2',!'Month',"Periodic")

    Hope it helps!

    Best regards,