Re: Compare line item month data to time dimension month data
Thank you Andre for the reply. Sure, please find attached the screenshot of the module.
I have Actuals line item in this Module which is derived from another module which makes it not readable in this module. I am trying to conditionally format the Actuals line item depending on the data in line item First Site Initiated Date. This is the date starting when the Actuals data should be populated.
I am trying to conditionally format Actuals line item data base on 3 conditions:
Lets say for example first site initiated date is April 2020 then the Actuals data should be populated starting April 2020 If it is then all is good and the whole Actuals row where data is populated should be Green
In a scenario where the first site initiated date is April 2020 and if the Actuals data is populated starting May 2020 this means there is one month of delay then the color should be Amber for April where the data is not populated
In a scenario where the first site initiated date is April 2020 and the Actuals data is populated starting June 2020 then April and May should be in Red color
I have another Module which has status and Months Delayed and have to refer this in my logic:
Please advise and Thank you again for your help!!!
You can achieve the desired results by doing the following:
Comparing the Current Period -ITEM(Time) - with the line item Period when the first site initiated.
Create a formatting line item (Number)
If Current Period >= Period when the first site initiated AND Actuals data is 0 (or ISBLANK()) then populate the formatting line item
Use the value of the formatting line item in the conditional formatting of the Actual Data line item you want to highlight.
This is the simplified version. You will need to think about how to count the "missing actuals" to adjust the formatting line item value and hence change the color of the conditional formatting based on the number of periods when actuals were missing.
If you run into obstacles or have further questions please let me know.
Einas "Give a Man a Fish, and You Feed Him for a Day. Teach a Man To Fish, and You Feed Him for a Lifetime"