Updating time dimension to the correct dynamic formula

hyunkangs
hyunkangs
edited November 2023 in Modeling
image.png

For this individual above, he started on December 2024 (H1 FY24) but the way I have the time settings set up it is 1 year before and 2 years after the current year and it is making this person start their proration at the beginning of the model calendar as opposed to their actual start date. I am trying to use these percentages to figure out their prorated quota but I cant quite figure out how to make it so that this individual, and others, would start the proration during the month that they have started. Below is the calculation field I have set up.


IF    'On Board and Quota Counts?'

THEN    'SYS030: Quota Proration'.Proration Factor[LOOKUP: 'SYS000: Time Settings'.'Month of Quarter (List)']

ELSE    0

Answers

  • rob_marshall
    rob_marshall

    @hyunkangs

    Try using if item(time)>=start(actual start date) then …..where actual start date is the line item identifying when the person started employment.

  • Dikshant
    Dikshant

    @hyunkangs,

    Build the active logic as below:

    image.png

    Trying to cover all corner cases for active logic:

    image.png

    Based on this active logic make you condition as:

    Formula: IF Employee Forecast By Month.Active THEN 'SYS030: Quota Proration'.Proration Factor[LOOKUP: 'SYS000: Time Settings'.'Month of Quarter (List)'] ELSE 0

    Let me know if you have any question.

Categories