Numbering Months

[#1]

Hi,

I am trying to create a table in Qlikview where I need number months starting Jan 2011 until August 2012.

Example:

Jan 2011 = month 1 and Aug 2012 = month 19. I have tried using the built num(month(field)) function and that only returns the months 1-12. Please let me know if anyone has a solution to this simple question.

Thank you,

Saurav

[Anonymous · Accepted solution]

If the table gets loaded in order (or do an ORDER BY (Date) in your load, then create a field AUTONUMBER(Month,Year) AS MonthYearNumber to get an integer for each month and year combination.

[whiteline]

=(year(Date) - year(StartDate))*12 + month(Date) - Month(StartDate) + 1

StartDate - Jan 2011 (or you can use min() function)

Date - your periods.