topic Re: Creating a Field in QlikView

Creating a Field

Wed, 16 Oct 2013 13:05:13 GMT

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

Re: Creating a Field

swuehl — Wed, 16 Oct 2013 13:10:29 GMT

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

Re: Creating a Field

tresB — Wed, 16 Oct 2013 13:15:07 GMT

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

Re: Creating a Field

Wed, 16 Oct 2013 13:17:31 GMT

Thank you Swuehl

Re: Creating a Field

Wed, 16 Oct 2013 13:30:09 GMT

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

Re: Creating a Field

tresB — Thu, 17 Oct 2013 04:34:25 GMT

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$]);

Re: Creating a Field

Thu, 17 Oct 2013 06:34:12 GMT

thank you Tresesco