Compare line item month data to time dimension month data

gautamgurwara

Hi,

In my module I have a line item that holds the value for Period when the Actuals should have the data. I have time dimension in columns by months

Is there a way I can write a formula and compare for example if the Period has April 20 then the formula would look at the column data for April 20 and check if Actuals line item has data or not? 

 

Thanks in advance!!

andre.lie

@gautamgurwara ,

 

Screenshot of your module will be helpful.  You can refer to the month in your time dimension using ITEM(TIME)

 

Regards

Andre

Misbah

@gautamgurwara 

 

Ask here is not clear. Can you please elaborate more or supplement it with a screenshot

einas.ibrahim

Hi @gautamgurwara 

I agree with others that the post is not clear.
However, this is a shot in the dark based on what you wrote in this phrase "compare for example if the Period has April 20"

 

The function ITEM(Time) will return the period 

You can then use the line value to compare.

 

rob_marshall

@gautamgurwara 

 

I think this is what you might be asking, but if not, please include a screenshot.

 

In my SYS Time Filter, I have a boolean telling which month is the current month.  In the below, I did this using CurrentPeriodStart(), but you also do this from a line item showing a date.

 

 

In my transactional module, I reference the Actuals line item in the above:

 

 

Rob

gautamgurwara

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:

1. 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
2. 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
3. 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:

Status	Months Delayed
Green	0
Amber	1
Red	2

 

Please advise and Thank you again for your help!!!

einas.ibrahim

Hi @gautamgurwara 

You can achieve the desired results by doing the following:

1. Comparing the Current Period -ITEM(Time) -  with the line item Period when the first site initiated.
2. Create a formatting line item (Number)
3. If Current Period >= Period when the first site initiated AND Actuals data is 0 (or ISBLANK()) then populate the formatting line item
4. 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.

andre.lie

@gautamgurwara , I agree with what @einas.ibrahim has suggested