Forum Discussion
Subtract two dates from one table
Hi,
I have one table with all the activities made on 1 appointment, as depicted on the image below. The activityId shows that the 2 records belong to the same appointment. Now I would like to have a measure or calculated column, which shows the difference in Days between the two records. I tried this in several ways, but couldn't get the desired result.
It's always the case that activityStatusValue 935000008 timestamp is subtracted by the timestamp of activityStusValue 935000000 for the same activityId.
All help is greatly appreciated!
Thanks,
Maurice
MauriceMecowe , Try a new column like
new column =
var _id =activityId
return
datediff(maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000008 ),[timestamp]),maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000000 ),[timestamp]), second)
or
new column =
var _id =activityId
return
ifr([activityStatusValue] = 935000000 , datediff(maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000008 ),[timestamp]),maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000000 ),[timestamp]), second) , blank())
1 Reply
MauriceMecowe , Try a new column like
new column =
var _id =activityId
return
datediff(maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000008 ),[timestamp]),maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000000 ),[timestamp]), second)
or
new column =
var _id =activityId
return
ifr([activityStatusValue] = 935000000 , datediff(maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000008 ),[timestamp]),maxx(filter(Table, [activityId] =_id && [activityStatusValue] = 935000000 ),[timestamp]), second) , blank())