Number of months between two dates

Option: Read this blog post in Swedish

At first glance, it can look tricky to count the number of months between two dates. There is not ready function for this in QlikView nor Sense. If one uses the age() function, one only gets the number of years.

Imagine that we have sales data connected with customers. We now want to see how much the customers spend distributed over the customer's age in months. We have an order date on the customer order and a registration date on the customer. One way to do the calculation is the following formula:

year(OrderDate)*12 + month(OrderDate) - (year(RegDate)*12 + month(RegDate))

The formula uses the functionality of month(date_field) that returns a numerical value of 1-12 for the month.

Let us test the function with order with a Order Date 2021-02-14 (14th of February 2021) and a customer with the registration date 2019-05-02 (2nd of May 2019). The formula then gives:

2021*12 + 2 - (2019*12 + 5) = 24252 + 2 - (24228 + 5) = 24254 - 24233 = 21

A manual control gives:

2019: 7 months

2020: 12 months

2021: 2 months

Total: 7 + 12 + 2 = 21

Notice that the formula doesn't consider when a customer was registered during the month or the order was placed.

We can show the values in a chart like:

---

Written by: Morgan Kejerhag

Morgan Kejerhag has worked with the Qlik platform since 2005 and is one of Sweden's most experienced consultants. During the years, Morgans has worked with several multinational companies where he has led the work to build both small and large Qlik environments. LinkedIn Contact Information