Creating Year field in forecast data based on Period no.

[hopkinsc]

Hi all,

I have some forecast data which is based on a rolling 13 period. I need to get the year created in the data too but it needs to change based on the period no.

eg.

If the current year is 18 (i have a field stating the current year) and the current period is 12 (again i have a field for this too)

then Id want 1812 applied to that row.

the same for period 13, it should be flagged as 1813.

but for period 1 - 11 id want the year to increment by 1, so it would show 1901, 1902, 1903, to 1911.

then when the next period starts, the new field would show..

1813

1901

1902

1903

1904

1905

to 1912

can anyone help please?

[sunny_talwar]

How do we know if the current period is 12 in 2018?

[hopkinsc]

Hi Sunny

There is a field 'Current Year' which states 118 and there is another field 'Current period' which states 12. there is also a field in the calendar which states how many periods are in the current year.

Is that what you mean?

[sunny_talwar]

that make sense, but why is year 118, not 18 or 2018? Just curious

[hopkinsc]

I have asked this question myself and was unable to get an answer! maybe the century.. its a very old system they have.

I will just strip out the leading 1

[sunny_talwar · Accepted solution]

Something like this?

[hopkinsc]

That's great, thanks again Sunny!

I've changed it slightly as the current period (12) was showing as 201912 when it should be 201812, so ive changed the IF statement to be >=.

[sunny_talwar]

Awesome

[hopkinsc]

Keep posted for something else!