Calculating days past due between dates in two different rows

Question & Answer
,
1

Hi All-
I’m trying to build an analysis in QuickSight to understand whether customers are delaying servicing of equipment over time. Each time a service is performed, a date is set for when the next service is due. I’m trying to create a calculated field with the number of days past due a service was completed.

image
image578×102 4.15 KB

In the example above, the initial service was completed on 2/1/2022 so the number of days past due would be null. Annual service was due on 2/1/2023 but wasn’t completed until 2/4/2023 so it was 3 days past due. The next annual service was then due 2/4/2024, but the service was actually completed early on 1/15/2024.

I’d be creating a visualization that shows the percentage of customer equipment that was due for service in a particular year that was completed by the due date. If the 3 rows above were all that existed, then 0% of services due in 2023 were completed by their due date, and 100% of services due in 2024 were completed by their due date.

Thank you in advance!

2

Hello @brettwesten

Welcome to QuickSight Community!!

From the example you have shared, I could find a calculation to support this:

Firstly for difference, I took difference in Month as follows: (you can modify the granularity as desired)

date-difference =

dateDiff(
{previous-service-date},
{max-service-date-agg},
"MM"
)

In the above calculation, I have used two other calculated fields to support it : I have used lag function to get the “service-due-date” that was set the previous year

previous-service-date = lag({next_service_due},[{next_service_due} ASC],1)

This lag function will throw Mismatched aggregate error if we try to directly perform a DateDiff using this Calculation, thus I had to create another aggregated CF that would give me the current year’s service-date

max-service-date-agg = max({service_date})

Now, with DateDiff function - I could get the difference, however for example in the third row, it will give negative value as servicing was done before the due-date - to address this I used if-else function to cater for null and negative values as well:

difference =

ifelse(
isNull({date-difference}),{date-difference},
ifelse({date-difference}>0,{date-difference},0)
)

It looked like this:

image
image2574×818 226 KB

I hope this helps!!

Thanks,
Snehali

1 Like
3

Hi @brettwesten,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

4

Hi @brettwesten,
Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!