Boolean Formula - Match line item with date to date on column i.e. item(time)

Highlighted
Contributor

Boolean Formula - Match line item with date to date on column i.e. item(time)

Hi,

 

I am trying to write a boolean formula that will create the check mark where the formula finds a match between another line item containing a date and the calendar date on my columns, i.e. item(time).

 

Attached are my current views and what I am trying to accomplish. Can someone please help me write this formula?

 

I do not know if it is possible or if I may need another module to look up to.

 

Thanks,

7 REPLIES 7
Highlighted
Community Boss

Re: Boolean Formula - Match line item with date to date on column i.e. item(time)

Because your calendar is daily, you can use the function start(), which returns the starting date of the calendar period. In your case, the date of the day.

You can simply compare with the formula:

start() = recalc date

 

Additional info: often we compare periods as well. For that it is better to create a line item called current period with a formula 

PERIOD(ITEM(TIME))

And you can compare that one with another line item period of another date.

 

Format these line items to the period you cant to compare (year,month, week, day). 


Nathan Rudman, Anaplan Model Builder
Highlighted
Community Boss

Re: Boolean Formula - Match line item with date to date on column i.e. item(time)

by the way START(ITEM(TIME)) works although it un-necessary.

Nathan Rudman, Anaplan Model Builder
Highlighted
Contributor

Re: Boolean Formula - Match line item with date to date on column i.e. item(time)

Thanks for your fast response and feedback, but I am still unclear as to what the formula needs to be for my line item 'Recalc'd Date Boolean' to accomplish what I want. Will you please provide a formula that will work?

Highlighted
Master Anaplanner/Community Boss

Re: Boolean Formula - Match line item with date to date on column i.e. item(time)

@jkinnick ,

 

One way of doing is to add the All Periods for Time:

2020-01-09_17-59-22.png

 

On your Date formula, change the summary to First Non Blank:

2020-01-09_18-00-44.png

 

The formula for recalc would be:

START() = Date[SELECT: TIME.All Periods]

 

2020-01-09_18-01-58.png2020-01-09_18-02-09.png

Does that help?  One problem with this is if the "date" line item can have multiple occurrences?  Let me see if I can come up with a better solution without using the All Periods trick.

 

Rob

Highlighted
Master Anaplanner/Community Boss

Re: Boolean Formula - Match line item with date to date on column i.e. item(time)

@jkinnick ,

 

Ok, a better/alternate way of doing this, is to have the recalc'd date in a different module (without time) associated with it.  Again, I am not sure what the requirements are, but I would take a guess the recalc'd date doesn't need to be dimensionalized by time.

 

2020-01-09_18-40-05.png

 

and then have the formula point to that.

 

2020-01-09_18-40-30.png

 

Hope this helps,

 

Rob

Highlighted
Contributor

Re: Boolean Formula - Match line item with date to date on column i.e. item(time)

@rob_marshall thanks for this solution, however, my time has to be setup by 'Weeks: General' for my model to work. See screenshot attached. I will try your second solution which looks like it has a good shot to work. Instead of employees we have properties and ownership groups, set up as lists, of those properties that have different monthly accounting close dates that vary by ownership group. 

Highlighted
Master Anaplanner/Community Boss

Re: Boolean Formula - Match line item with date to date on column i.e. item(time)

@jkinnick 

 

yes, I saw you have Properties, I just used a list that I had to simulate it, in my case Employees.  The 2nd solution should work well for you.

 

Thanks,

 

Rob