Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
melissa4
Contributor II
Contributor II

Date, Date#, and AddMonths Question

I am trying to do the following and am unable to figure it out. Does anyone know how I can achieve this?

Goal
I have 2 fields: Year and Month_Nbr. In the end, I want them concatenated into the format 'yyyymm'. However, I need it to display 1 month prior to its value.

For example, 201902 would display as 201901; and 201901 would display as 201812; meaning the year would also need to decrease by 1 if the month is January.

This is the formula I came up with, which doesn't work because it reduces the year by 1, not the month.
        Date(AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm'),-1),'yyyymm')

These are the steps I took:

  1. Started with a Year field and a Month_Nbr field

  2. Converted the month to include leading zero  
    • Num(Month_Nbr,'00')
  3. Concatenated the year field and month field   
    • Num(Year) & Num(Month_Nbr,'00')

  4. Convert the concatenated field to 'yyyymm'
    • Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm')

  5. Subtract a month
    • AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm'),-1)

  6. Returned to 'yyyymm' format 
    • Date(AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'),'yyyymm'),-1),'yyyymm')

Result:     201902 becomes 201802
Desired:  201902 should become 201901

Any guidance is appreciated, thank you.

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

I think you might have missed the trick by using mm instead of MM. M is for Month and m is for minutes

View solution in original post

3 Replies
sunny_talwar

Try this

Date(AddMonths(Date#(Num(Year) & Num(Month_Nbr,'00'), 'YYYYMM'), -1), 'YYYYMM')
sunny_talwar

I think you might have missed the trick by using mm instead of MM. M is for Month and m is for minutes

melissa4
Contributor II
Contributor II
Author

Thank you, that did it!