Serialize month and year

[sifatnabil]

Hi all,

I would like the month & year (e.g. Apr 2016) to be serialized where each month increases by 1. So for example, if my data starts from Oct 16, the serial field should look like this:

MonthYear	Serial
Oct-16	1
Nov-16	2
Dec-16	3
Jan-17	4

How can this field be created? Thanks.

[marcus_sommer · Accepted solution]

Try it with: autonumber(MonthYear) as MonthYearSeriell

Edit: For this you will need a sorted load which is already quite common by using a master-calendar.

- Marcus

[MK_QSL]

Data:

Load

  *,

  If(MonthYear = Date#('Oct-16','MMM-YY'),

  1,

  1+ Year(MonthYear)*12+Month(MonthYear)-(Year(Date#('Oct-16','MMM-YY'))*12+Month(Date#('Oct-16','MMM-YY')))) as ID

;

Load

  Date#(MonthYear,'MMM-YY') as MonthYear

Inline

[

  MonthYear

  Oct-16

  Nov-16

  Dec-16

  Jan-17

];