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

jk1nn1ck_*20973
jk1nn1ck_*20973
in Integrations and extensibility

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,

Tagged:

Comments

  • nathan_rudman
    nathan_rudman

    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
    nathan_rudman
    by the way START(ITEM(TIME)) works although it un-necessary.
  • jk1nn1ck_*20973
    jk1nn1ck_*20973

    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?

  • rob_marshall
    rob_marshall

    @jk1nn1ck ,

     

    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

  • rob_marshall
    rob_marshall

    @jk1nn1ck ,

     

    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

  • jk1nn1ck_*20973
    jk1nn1ck_*20973

    @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. 

  • rob_marshall
    rob_marshall

    @jk1nn1ck 

     

    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

Categories