Creating a Field

[#1]

Hi

I have a table called Period.  See attached file. In the table i have a filed called MonthIndex.

I want to create specific additonal fields for Month Index

for eg  Max (MonthIndex)          as [Current Month]

          Max(MonthIndex) - 1       as [1 Month Ago]

          Max(MonthIndex) - 2       as [2 Months Ago]

How do i do this.

I know i can create this is "Variable Overview" under settings, howver i want to create it in the background script.

The reason for asking tihs query (ie creating a field) is that i have a formula in the pivot table, which i think, only works with recognised fields in the background.

I have attached the QV Model and Data (in excel).

kind regards

Nayan

[swuehl]

I would suggest that you create a master calendar, and use relative calendar fields as described in these blog posts:

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/16/the-master-calendar

http://community.qlik.com/blogs/qlikviewdesignblog/2013/06/10/relative-calendar-fields

[tresesco · Accepted solution]

If your MonthIndex field is having numeric date values, try like:

Month(Max(MonthIndex)) as CurrentMonth

Month(Addmonths(Max(MonthIndex),11)) as OneMonthAgo

Month(Addmonths(Max(MonthIndex),-2)) as TwoMonthsAgo

[#4]

Thank you Swuehl

[#5]

thank you Tresesco.  When i do the scripting, do i create a temp:  for eg

Directory

;

Period:
LOAD

Cal.Month,
Cal.Per,
Cal.Quarter,
Cal.Year,
Cal.Year_Month,
Cal.Year_Per,
Fisc.Per,
[Fisc.Per-Month],
Fisc.Quarter,
Fisc.Year,
MonthIndex,
[Monthly Figures Key],
YrMax

FROM

[Period Table.xls]
(biff, embedded labels, table is [Sheet1$]);

[Period Temp]:

load

Month

(Max(MonthIndex)) as CurrentMonth,

Month(Addmonths(Max(MonthIndex),11)) as OneMonthAgo,

Month(Addmonths(Max(MonthIndex),-2)) as TwoMonthsAgo

Resident Period

[tresesco]

Temp would not be required, you can get it in the same load like:

Period:
LOAD

Cal.Month,
Cal.Per,
Cal.Quarter,
Cal.Year,
Cal.Year_Month,
Cal.Year_Per,
Fisc.Per,
[Fisc.Per-Month],
Fisc.Quarter,
Fisc.Year,
MonthIndex,
[Monthly Figures Key],
YrMax,

Month(Max(MonthIndex)) as CurrentMonth,
Month(Addmonths(Max(MonthIndex),11)) as OneMonthAgo,
Month(Addmonths(Max(MonthIndex),-2)) as TwoMonthsAgo

FROM

[Period Table.xls]
(biff, embedded labels, table is [Sheet1$]);

[#7]

thank you Tresesco