Forum Stats

  • 3,824,872 Users
  • 2,260,435 Discussions


How to Subtract multiple instance dates to one another, also considering Blank as an Input?

Hi OIA community,,

I have a problem Statement as, wants to subtract N number of Input dates from each other in the same instance considering blank and duplicate dates in an Input as well. Need to find out from 4 instance which instances difference is <=30 months and need to know which instance ?? In the below example it is Instance 3 , 4 and 1. All 4 below instance is 1 data set. Below is the business requirement to code.

Example 1 : Entity = Bill , Occurrence date = (User Input date) , System date = 2022-06-23

1 2021-03-01(15 months from system date) Difference in dates 1 and 2 = 69 Months

2 2015-06-01(84 months from system date) Difference in dates 2 and 3 = 66 Months  

3 BLANK (18 months Default) Difference in dates 3 and 4 = 3 Months (Blank)

4 2021-03-01(15 Months from system date) Difference in dates 1 and 4 = 0 Months (Duplicate)  

Difference in dates 4 and 2 = 69

Difference in dates 1 and 3= 3 months

Answer Expectation =

  • Which Instance was responsible for <=30 months difference.
  • Store the each date difference in number format in variable. (Need to use this in further logic)




  • Richard Napier
    Richard Napier Member Posts: 417 Bronze Trophy

    Hi Shital

    I'm sorry but your example is not clear (to me). Could you please - for example - please detail the following

    1) Each attribute that is stored in the entity

    2) What you mean by the following

    "Example 1 : Entity = Bill , Occurrence date = (User Input date) , System date = 2022-06-23

    2021-03-01(15 months from system date) Difference in dates 1 and 2 = 69 Months"

    So the entity is called "the bill". The user enters an occurrence date (per bill?) and there is the system date.

    Instance one "2021-03-01" is this the occurrence date? Let's assume it is.

    So the difference between the occurrence date and the system date is 15 months.

    Then you say "Difference in dates 1 and 2 = 69 months"

    What is date one, date two? Are you referring to the instances?

    Instance 1 : Occurrence Date 2021-03-01 System Date 2022-06-23 therefore 15 months from system date

    Instance 2 : Occurrence Date 2015-06-31 System Date 2022-06-23 therefore 84 months from system date

    Thus 84 minus 15 = 69?

    That's going to be a challenge unless you can uniquely identify the previous instance in some sort of order - using an iterative inferred entity and relationships like the next instance and the previous instance which has been discussed here before.

    Then the next two instances (instance 3 and instance 4) do not seem to follow the same pattern, especially instance 4.

    3 BLANK (18 months Default) Difference in dates 3 and 4 = 3 Months (Blank)

    2021-03-01(15 Months from system date) Difference in dates 1 and 4 = 0 Months (Duplicate)  

    Why does instance three compare with instance 4, rather than instance 2 (as in the previous two instances?)

    I would suggest that you need to break the logic down into much smaller components, such as :

    Relating each instance to the other instances you intend to compare using a rule, creating the intermediate attributes to handle the month difference between each instance and finding the highest month difference for any instance with it's other instances.

    In it's current state I would say it looks like a) there are requirements that are not clear and b) the rule-based logic is hard to discern and c) you certainly will need to approach this iteratively, and most likely create one or more inferred entities and relationships to support a structure that allows you to make the calculations.