Difference between two years output into a bar chart

[Wbbell216]

Hi! New to Qlik and I was tasked with figuring out how to get the difference between two dates and output that difference in years into a bar chart in order to assess data quality. This is the code I've tried: 

=Interval([NFADate.autoCalendar.Year] - [ReportedDate.autoCalendar.Year],'Y')

However the output looks like this (identifying information censored, the x axis is just the IDs of each location)

Not sure what I'm doing wrong, I'd really appreciate any help on this!

[nhenckel · Accepted solution]

If you’re looking for the difference in years between two dates, including decimals (e.g., 0.99 years), it’s best to use the full timestamp values (NFADate and ReportedDate) and calculate the difference accurately.

Recommended Approach:

Use the following expression to calculate the exact difference in years:

=Round((NFADate - ReportedDate) / 365.25, 2)

Why this works:

• Subtracting two dates in Qlik gives the difference in days.
• Dividing by 365.25 accounts for leap years and converts the difference to years.
• The Round() function ensures you get the result rounded to two decimal places.

---

Best Practice: Do This in the Load Script

While you can calculate the difference in years in the front-end chart, it’s often cleaner and more efficient to handle such transformations in the Load Script. This way, the bar chart can simply pick up the pre-calculated field.

In your Load Script, add this calculation:

LOAD 
    *,
    Round((NFADate - ReportedDate) / 365.25, 2) AS YearDifference
FROM YourDataSource;

This creates a new field called YearDifference, which can be used directly as the dimension or measure in your bar chart.

---

Simplified Approach: Whole Years Only

If you’re only interested in the difference in whole years (e.g., 2024 - 2024 = 0), you can simplify the calculation like this:

=NFADate.autoCalendar.Year - ReportedDate.autoCalendar.Year

[Vegar]

If the fields [NFADate.autoCalendar.Year] and  [ReportedDate.autoCalendar.Year] are integers with the year value, do today 's date would be 2024, and the coming January 2025, then you just subtract the two. 

=NFADate.autoCalendar.Year - ReportedDate.autoCalendar.Year

[Wbbell216]

Thank you so much for the thorough and thoughtful answer! I really appreciate the explanations as some people just post a bunch of code and expect you to decipher it yourself, which is difficult as a beginner. I decided to go with the recommended approach as the third approach kept giving me the output in the thousands which doesn't make sense and I'm not too worried about efficiency at this point. The recommended approach gave me exactly what I needed! Thanks!