Updating time dimension to the correct dynamic formula

Options
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
    Options

    @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
    Options

    @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