Unable to get difference in dates in DMN table

skbabu · September 28, 2021, 6:12am

Hi Everyone,
I am trying to get the difference between two dates in DMN table. I can understand we have date range and also >= or <= options available. But can’t find date difference function. I found the below logic available in the link https://documentation.signavio.com/suite/en-us/Content/process-manager/userguide/dmn-literal-expressions.htm#Datetimeoperations[https://documentation.signavio.com/suite/en-us/Content/process-manager/userguide/dmn-literal-expressions.htm#Datetimeoperations]. However, this isn’t working. Kindly advise.

DayDiff

DayDiff(datetime1, datetime2):NUMERIC

Returns the amount of full days between two dates.

Example: DayDiff(2015-12-24T12:15:00.000+01:00, 2015-12-25T12:15:00.000+01:00) returns 1.

hassang · September 28, 2021, 7:00am

Hi @skbabu,

Please have a look at below docs (more specifically the “Subtraction” section)

“Temporal Expressions | FEEL-Scala” Temporal Expressions | FEEL-Scala

skbabu · September 28, 2021, 7:33am

Hi @hassang ,

Thanks for the inputs. I tried the options from the given link. When I tried subtracting the dates in the format date(“2021-02-01”) - date(“2021-01-01”) with the output type as String and get back the result in duration format as below.
“dateCheck”: {
“type”: “String”,
“value”: “PT744H”.
I want to get the number of days as a result between the two provided dates as a numeric vale (it would be 31 in the above example) so that I can multiply it with another amount value which would be of double data type. PaymentFrequencyChange.dmn (4.0 KB)
Below is the input request and I have attached the DMN for your reference. Kindly advise.

{

"variables": {

    "toPaymentFrequency": {

        "value": "monthly"

    },

     "originalPremium": {

            "value": 1000

    },

     "commissionRateNB": {

            "value": 0.6

    },

     "baseInceptionDate": {

            "value": "2021-01-01T00:00:00"

    },

     "newInceptionDate": {

            "value": "2021-02-01T00:00:00"

    },

     "renewalDate": {

            "value": "2022-01-01T00:00:00"

    }

}

}

hassang · September 28, 2021, 7:56am

Hi @skbabu

Please try below expression

(date(“2021-02-01”) - date(“2021-01-01”)).days

skbabu · September 28, 2021, 8:26am

Awesome. Thanks a tonne @hassang . This works perfectly. However, DMN accepts only the input type of Date Time in the format 2021-02-01T00:00:00 in the Postman API request. I am unable to convert it to Date type in the format “2021-02-01” to find the difference in the Camunda DMN table. Is there an option to achieve this ? Please advise.

hassang · September 28, 2021, 8:37am

Hi @skbabu,

Change input type from Datetime to String and do the conversion to Datetime type as part of the dmn expression

skbabu · September 28, 2021, 8:45am

Thanks again @hassang for the quick turn around. You are a star. This logic works like a charm. Have a nice day.

skbabu · September 30, 2021, 7:54am

Hi @hassang ,
Hope you are well. Sorry to bother you again. Is there a way to get the difference in months as output for the two given dates in Camunda DMN table. For example , If I need to get the difference between the dates 2022-01-01 and 2021-02-01, I should get the result as 11 which means the difference is 11 months. I tried month(date(“2022-01-01”)) and also (date(“2022-01-01”) - date(“2021-02-01”)).months . But both of these doesn’t work. Kindly advise if there is any other option to achieve this. Thank you.

hassang · September 30, 2021, 8:22am

Hi @skbabu

You need to convert day-time-duration to year-month-duration in order for months property to work.

Try below expression

years and months duration(date("2021-02-01"), date("2022-01-01")).months

skbabu · September 30, 2021, 11:47am

Hi @hassang ,

Thanks for the update. I can get the result as “**Discounts1.dmn (2.8 KB)
**” when I try the expression (date(“2022-01-01”) - date(“2021-02-01”)). However, I am getting error when I try the expression you provided below.
years and months duration(date(“2022-01-01”) - date(“2021-02-01”)).months.
Not sure how to get that conversion from day-time-duration to year-month-duration.

Below is the request I am trying. I have attached the DMN as well for your reference. Kindly assist.

{

"variables": {

    "adviserChannel": {

        "value": "Test1"

    },        

    "baseInceptionDate": {

        "value": "2021-01-01"

    },        

    "newInceptionDate": {

        "value": "2021-02-01"

    }

}

}

hassang · September 30, 2021, 12:44pm

Hi @skbabu,

My bad…
When years and months duration function is used, from and to dates are passed as parameters so comma should be used instead of subtraction symbol

years and months duration(date("2021-02-01"), date("2022-01-01")).months

I have edited my previous answer.

Edit: I noticed that incorrect double quotes have been used in your post (“ instead of ") so please copy the above expression and try

Kindly find a working example which you can test using online simulator
test-dmn.dmn (1.5 KB)

https://consulting.camunda.com/dmn-simulator/

skbabu · September 30, 2021, 11:04pm

Hi @hassang ,
Thanks for the update. Yes. The given example works correctly for years and months duration(date(“2021-02-01”), date(“2022-01-01”)).months which gives the result as 11. However, I noticed this logic doesn’t work correctly when the difference in months > 11. For example , years and months duration(date(“2021-01-01”), date(“2022-01-01”)).months gives me the result of zero instead of 12. Also, years and months duration(date(“2021-01-01”), date(“2022-05-01”)).months returns the result of 4 instead of 16. Is there a way to get this fixed ? Kindly advise.

hassang · October 1, 2021, 2:36am

Hi @skbabu,

Again…my bad… You are right

When result is 16 months, duration is returned as 1 year and 4 months (year-month-duration).

months property returned the months part which is 4 (as per the docs, months property returns the normalized months component as number [0…11]).

I believe below should do the job even that it doesn’t look like an elegant solution.
years and months duration(date("2021-01-01"), date("2022-05-01")).months + years and months duration(date("2021-01-01"), date("2022-05-01")).years * 12

Kindly find a working example
test-dmn.dmn (1.6 KB)

skbabu · October 1, 2021, 6:54am

Hi @hassang ,

Thank you. This works even though the formula logic is too long, it gives the desired result. Wondering if there is any Camunda Docs link available to refer for these type of calculation logics defined that can be used within the DMN table…

Another question which I hope you might be able to help. I want to get the boolean result from the output calculated column. I have an Output column with double datatype which does the calculation from the input columns and returns the result which could be either a positive or negative value. Based on this output value I should have another Output column in the same table that says True if the result value is Positive and False if it’s negative. Is that something that is achievable ? Kindly advise. Something similar to the below.

hassang · October 1, 2021, 7:57am

Hi @skbabu

You can only use input expression in the expression of an output entry so below expression can be used for Result variable

(InputA * InputB) > 0

system · January 30, 2024, 1:23pm