Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
As a member of the Demo Team, I often use random, dummy data to build demos. If the data is not exactly how I would like it to be, I can tweak it to best illustrate Qlik products and features. One of the tweaks that I often make is to update the dates in the data model – I take an old data set from a few years ago and refresh it so it looks more current. The AddYears and AddMonths functions make this easy to do.
The AddYears and AddMonths functions can be used in the script and in a chart. Here is how the AddYears function is defined in Qlik Sense Help.
AddYears
This function returns the date occurring n years after startdate or, if n is negative, the date occurring n years before startdate.
Syntax:
AddYears(startdate, n)
The AddYears function adds or decreases a date by the number of years indicated in the second parameter. If the parameter is positive, the date is increased, and if the parameter is negative, the date is decreased. In the table below, the Date column has the original date. The AddYears(“Date”, 5) column increases the date by 5 years. The AddYears(“Date”, -5) column decreases the date by 5 years. You can see how easy this to use to refresh the dates in your data model.
The AddMonths function works the same way except the AddMonths function has a third parameter to set the mode. The syntax looks like this:
AddMonths
This function returns the date occurring n months after startdate or, if n is negative, the date occurring n months before startdate.
Syntax:
AddMonths(startdate, n , [ , mode])
The mode, as defined in Qlik Sense Help, “specifies if the month is added relative to the beginning of the month or relative to the end of the month. If the input date is the 28th or above and mode is set to 1, the function will return a date which is the same distance from the end of the month as the input date. Default mode is 0.”
Let’s look at some examples. In the table below, the AddMonths(“Date”, 3) column add 3 months to the date in the Date column and AddMonths(“Date”, -3) shows a date 3 months prior to the date in the Dates column. By default, mode is 0 so by excluding it I am assuming the default value. So you can see that AddMonths(“Date”, 3, 0) returns the same results as AddMonths(“Date”, 3). If I set the mode to 1, AddMonths(“Date”, 3, 1), it will take into account the distance of the date from the end of the month when the day is the 28th or above. You can see that for the Dates 1/1/2013 and 1/15/2013, AddMonths(“Date”, 3, 1) returns 4/1/2013 and 4/15/2013, respectively, as you would expect. But for 1/30/2013, the results are a little different. Since the 30th is past the 28th day of the month, the function checked to see how many days from the end of the month the day falls. In this case, the 30th is 1 day from the last day of the month (which is the 31st for January). So the results of AddMonths(“Date”, 3, 1) will return a date 3 months later and 1 day from the last day of the month, hence 4/29/2013 since there are 30 days in April.
The AddYears and AddMonths functions can also be used to create a new date in your data model. For instance, if you need to create a new date field that is one month after a transaction date, you can do that in the script with an expression like this:
AddMonths(“Transaction Date”, 1) as “New Date Field”
There are many ways these functions can be used in your app. The one thing to keep in mind is if you are going to increase or decrease the dates in your data model, you should do so consistently. Make sure you are updating all dates in the data model by the same time frame. This way, date relationships in the data model are maintained throughout the app. You may also need to update other date related fields like day, week, month, year, etc. if you are using these fields in your app.
Thanks,
Jennell