Compare numbers in different modules

edited February 2023 in Modeling


I have a module with employee details to calculate their monthly payout. The payout is based on the % attainment stored in the same module. The % attainment need to fall in a defined range (captured in a DAT module) to suggest the payout %. For example the DAT module is as given below (not dimensionalised)

The other module (emp module) has projected attainment % for each employee (like below)

We need to compare the Attainment in 2nd screenshot to the Achievement(which is a range) in the 1st screenshot to pull the payout % from Screenshot 1 to 2.

cc @Misbah @JaredDolich

Best Answer

  • megha09
    Answer ✓

    Hi @yatin.ajbani

    Here is the solution that you can use. For first module (Let's call it achievement module) you will have to dimension it by ID and would need to create an extra line item called High so that it defines the range of Achievement in Achievement module. Here is a screenshot -

    Next you need a relation for attainment to fall in this range of achievement, for that you will have to create a Range AKA Tier Module that will be dimensioned by Employee and ID list which will identify the ID it should fall in. Here is a screenshot for the same -

    Lastly, when you have established relationship with your range of achievement and corresponding attainment% you can lookup using ID to find you corresponding Payout in your employee module. Here is a screenshot of same -

    Hope this helps. For more understanding you can also refer to