Posted: 11 Feb 2020 12:24 EST Last activity: 28 Feb 2020 16:41 EST
Difference in Two Dates
I wanted to have a column in our report definition where we would like to calculate the difference in DAYS between two datetime properties(pxcreatedDateTime and pyResolvedTimeStamp).
I wanted to calculate the difference between these two dates in minutes to seveal months or days.
if the difference is 2 minutes, it should show 2 minutes in the report definition column.
If the Difference is 6 months, Its should show 6 months.
But I just wanted to point out one weak requirement in your statement - "if 2 minutes, then 2 minutes, if 6 months then 6 months".
You have to define more clearly (for yourself) how to define when to show, for example, hours and when - days. These are quite close measurement units, so you could have 36 hours or 1d12h (or just 1d if it more suit your needs)
Does a month always mean 30 days in your report? A year always 365 days? You need to decide this ahead of time.
If the above is true, the easiest way may be to get the difference in minutes using the OOTB function alias, then do the math to find how many whole years, months, days, hours and leftover minutes. Then concatenate the values together into a string.
I would also take advantage of the feature that function alias calls can be nested (one FA can call another) to break down the main FA into a bunch of simple ones.