Conditional Format for the future Periods

CommunityMember82499
CommunityMember82499
in User Experience

Hi Experts,

 

I have a query related to dashboard, I have a line item called "Potential Retirement Date" in date format.

 

1. Whenever the date/year is 2019(current year) + 2 years(till 2021) it should show Orange color.

2. Whenever the date/year is 2018(Previous Year) is should show Red color.

3. Whenever the date/year is more than 2 years of current year i.e. from 2022 it should show no color.

 

I have done with the above 1 & 2 points, but in 1st point it is working fine to 2019 + 1 year and not +2 years, cuz in the model calendar the number of future years is 1. so it is working fine.

It is working fine for the previous years as no of past years is 2.

So Could someone help me with the solution.

 

Thanks

Sasikanth

Tagged:

Answers

  • DavidSmith
    DavidSmith

    @CommunityMember82499 

    What I would do is create a time system module and check for the first and last periods

    2019-05-24_08-45-50.png

    1st Period? = OFFSET(1, -1, 0) = 0
    Last Period? = OFFSET(1, 1, 0) = 0
    Timescale Start = IF '1st Period?' THEN START() ELSE BLANK
    Timescale End = IF Last Period? THEN END() ELSE BLANK 

     

    Then you can bring these dates into a lookup module:

    2019-05-24_08-49-34.png

    Start of Timescale = TIMESUM(Time Settings.Timescale Start)
    End of Timescale = TIMESUM(Time Settings.Timescale End)

    Future Date Check = ADDYEARS(End of Time Scale, 'Threshold (Yrs)')

    The threshold is for you to adjust as and when necessary. 

     

    Best practice tip: It is better to parameters stored in a constants/lookup module to avoid hard coding

     

    You can then reference the values in the above module to derive the numeric values needed for conditional formatting

    I hope this helps

    David

  • CommunityMember82499
    CommunityMember82499

    Hi David,

     

    Thanks for the detailed description but I could able to articulate my line item "Condition" to the newly created modules, could you please help me with the formula for the same.

     

    That Condition should works for the "Potential Retirement Date" line item.

     

    Thanks

    Sasikanth

  • DavidSmith
    DavidSmith

    It would be something like

     

    IF Potential Retirement date > Future Date Check then x else ....

     

    So to get no colour you'll need to use a 3 colour conditional format with "blank" in the middle and use lower, mid and high to reference the colours

    The formula above will be the middle number of the formatting

    I usually use -1 , 0, 1 for the parameters to keep it simple

     

    So IF low condition THEN -1 else IF high condition THEN 1 ELSE 0

     

    Hope that helps

    David

     

     

Categories