IF statement with different hierarchies

BryanByrnes
BryanByrnes
edited December 2022 in Modeling

Below, I have this module dimensioned by O2 Department (Level 2 in the hierarchy) and another list. My IF statement always returns FALSE when I have some start dates that do match the first day of the month. DAT03 New Hire Details is dimensioned by New Hires (Level 4 in the hierarchy). I believe I have a hierarchy issue somewhere. Any ideas on how to get this formula to work and return TRUE when it's supposed to?

 

BryanByrnes_0-1670947029004.png

BryanByrnes_0-1670947390767.png

BryanByrnes_0-1670947429999.png

 

 

Tagged:

Answers

  • ManjunathKN
    ManjunathKN
    edited December 2022

    Hi @BryanByrnes ,

    If I got your problem statement correct, Dat04 is having a parent hierarchy so, you should do a SUM of parent. 
    but, before to that you are also referring a date logic from Dat03 to Dat04 (from L2 to L4).  

    So here is how I would solve,

    I would create 2 Line items in Dat03 
    -->LI 1 - Start(period(Start Date)) = Sys02.first day of month - Boolean formatted - Summary none
    -->LI2 - if LI 1 then 1 else 0 - Number formatted - summary sum

    Now in Dat04 I would create 1 more line item,
    --->LI 1 - Parent(parent(L2)) - List formatted (L2)
    --->Total Future Hires - IF Dat03.LI2[sum: Dat04.LI1]>0 then Dat03.New hires[sum: Dat03.Department, Sum: Dat03.Pay type, Sum: Dat04.LI1] else 0

    Thanks,
    Manjunath

  • BryanByrnes
    BryanByrnes
    edited December 2022

    Thanks for the quick response. DAT03 doesn't have time as a dimension so LI1 is never true.

  • ManjunathKN
    ManjunathKN

    @BryanByrnes ,

    Correct, I missed that. basically what you can do is to create a staging module for Dat03 for only that start date Line item along with time and use this line item instead of LI 1 boolean formatted in Dat03.

    Thanks,
    Manjunath

Categories