Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have 2 date fields and the requirement is to first calculate the difference of the days and then display only data that has 45 days difference.
This is a straight table with the column names - Case ID and the 2 date columns are - Email_Sent_Date and Case_Closing_Date
I added one more column and used the following expression in the measure -
Interval(Case_Closing_Date -Email_Sent_Date,'d')
Above formula gives me the difference correctly in days but I am trying to rename this column as Date Difference and also I wanted to display only those Case ID 's data where the date difference is = 45 days only
Please help me how I can achieve that.
Thank you .
@pgloc2020 also, you need to put expression as measure, if you are using it in dimension, use below. then this option will work
=if(Case_Closing_Date -Email_Sent_Date <=45, Case_Closing_Date -Email_Sent_Date ,null())
@pgloc2020 Intreval is just formatting function, which is actually not giving values in days. SO if you further want to compare with difference , you need to convert it to get actual day number, hence I have used floor over date to get days difference
=if(floor(Case_Closing_Date) -floor(Email_Sent_Date ) <=45, floor(Case_Closing_Date) -floor(Email_Sent_Date ),0)
if your Date doesn't contain time factor, you can simply use below to get days difference
=if(Case_Closing_Date -Email_Sent_Date <=45, Case_Closing_Date -Email_Sent_Date ,0)
uncheck suppress zero values option
Thank you so much for the quick reply. I used the expression you mentioned but even by unchecking suppress zero values option, I am getting 0 and 45. Please see the below SS-
@pgloc2020 it is suppress null value.. for suppress zero values, go to property ->Add-Ons
@pgloc2020 also, you need to put expression as measure, if you are using it in dimension, use below. then this option will work
=if(Case_Closing_Date -Email_Sent_Date <=45, Case_Closing_Date -Email_Sent_Date ,null())