1

Date Difference Formula Field-Dashboard

Hi,

I have two fields-PO Delivery Date and Promise Delivery Date

I would like to add a formula field that should calculate the difference in terms of no of days from both the above-listed fields?

 

 

I have written below mentioned query but it's not giving the correct result? In case the promise date field is blank then the new formula field value should be blank,because here only my Po Delivery date is mandatory and promise delivery date is optional.

could you write an exact query that should I write in the formula?

 

 

i am getting this incorrect result?

9replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Can you please provide sample data? 

    if you are concerned about numbers like this -106751973181? then it is due to blank values on Promise Delivery Date field.

    To calculate the number of days between two dates... I'd use daysbetween(end_date, start_date)

    Like
  •  file attached herewith,  if it blank value it should put difference as Zero, or null or any other predefined text? how to do this pls help.

    and where value is coming like this -371 or -323 this is also not correct, what is the issue here?

     

     

    Like
  • try this formula below. replace the fields with the fields from your schema.table.field

    Attached my dashboard output and I validated incorta output with excel "Days" formula and it ties out.

     

    if(

        isNull(

            Test.ERP_DATA.Promised_Delivery_Date

        ),

        null(),

        daysBetween(

            timestamp(

                Test.ERP_DATA.Promised_Delivery_Date

            ),

            timestamp(

                Test.ERP_DATA.Po_Delivery_Date_Erp

            )

        )

    )

    Like 1
      • rakesh
      • PSP
      • rakesh
      • 1 yr ago
      • Reported - view

      Siva Kowsika  thanks for the help, its working fine now.

      Like
  • Hi,

    I have two fields-Last Received Date and Last Usage Date

    I had added a formula field that should calculate the difference in terms of no of days from both the above, but in some cases there is not date in Last Received Date field and some cases in Last Usage Date

    below formula is added

    if(

        isNull(

            SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_RECEIVED_DATE

        ),

        null(),

        daysBetween(

            timestamp(

                SL9_INVENTORY.BV_ITEM_USAGE.LAST_USAGE_DATE

            ),

            timestamp(

                SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_RECEIVED_DATE

            )

        )

    )

    but its worked only in the case where 'LAST_RECEIVED_DATE' is blank

    Like
    • hi Anand I think you want to check both dates for null so replace this expression: 

      isNull(

              SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_RECEIVED_DATE

          ) with this one:

      or(

      isNull(

              SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_RECEIVED_DATE

          ),

      isNull(

              SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_USAGE_DATE

          )

      )

      Thanks,

      Dustin

      Like
      • Anand
      • NOV
      • Anand
      • 7 mths ago
      • Reported - view

      Dustin Basil thanks for reply

      I have updated formula as below

      if(

          or(

              isNull(

                  SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_RECEIVED_DATE

              ),

              isNull(

                  SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_USAGE_DATE

              )

          ),

          null(),

          daysBetween(

              timestamp(

                  SL9_INVENTORY.BV_ITEM_USAGE.LAST_USAGE_DATE

              ),

              timestamp(

                  SL9_INVENTORY.BV_INVENTORY_DETAILS.LAST_RECEIVED_DATE

              )

          )

      )

      but now the result show as #ERROR

      Like
    • Anand that formula is syntactically correct, I tested it with my own data set. Can you inspect the Analyzer tenant log file? There will be more details about the cause.

      Like
      • Anand
      • NOV
      • Anand
      • 7 mths ago
      • 1
      • Reported - view

      Dustin Basil okay, but how we can check the Analyzer tenant log file

      Like 1
Like1 Follow
  • Status Answered
  • 1 Likes
  • 7 mths agoLast active
  • 9Replies
  • 143Views
  • 5 Following

Product Announcement

Incorta 2014.2 is now available!