Compare line item month data to time dimension month data

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!!

Accepted answers

All comments

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:

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:

Status	Months Delayed
Green	0
Amber	1
Red	2

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

2020-06-10_7-27-06_73427.jpg

einas.ibrahim

Hi @gautamgurwara

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.

andre.lie

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

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…