Display filtered data based on the difference of 2 dates

[pgloc2020]

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 .

[Kushal_Chawda]

@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

[pgloc2020]

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-

[Kushal_Chawda]

@pgloc2020  it is suppress null value.. for suppress zero values, go to property ->Add-Ons

[Kushal_Chawda · Accepted solution]

@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]

Thank you @Kushal_Chawda . This works.