topic Re: Creating Year field in forecast data based on Period no. in QlikView

Creating Year field in forecast data based on Period no.

hopkinsc — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Creating Year field in forecast data based on Period no.

sunny_talwar — Fri, 18 May 2018 10:30:03 GMT

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

Re: Creating Year field in forecast data based on Period no.

hopkinsc — Fri, 18 May 2018 10:32:54 GMT

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?

Re: Creating Year field in forecast data based on Period no.

sunny_talwar — Fri, 18 May 2018 10:40:58 GMT

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

Re: Creating Year field in forecast data based on Period no.

hopkinsc — Fri, 18 May 2018 10:42:44 GMT

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

Re: Creating Year field in forecast data based on Period no.

sunny_talwar — Fri, 18 May 2018 11:13:48 GMT

Something like this?

Re: Creating Year field in forecast data based on Period no.

hopkinsc — Fri, 18 May 2018 12:53:26 GMT

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 >=.

Re: Creating Year field in forecast data based on Period no.

sunny_talwar — Fri, 18 May 2018 12:54:29 GMT

Awesome

Re: Creating Year field in forecast data based on Period no.

hopkinsc — Fri, 18 May 2018 12:55:27 GMT

Keep posted for something else!