1Date 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?
-
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)
-
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?
-
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
)
)
)
-
Siva Kowsika thanks for the help, its working fine now.
-
-
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
-
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
-
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
-
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.
-
Dustin Basil okay, but how we can check the Analyzer tenant log file
-
