Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
Thank you Swuehl
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
Temp would not be required, you can get it in the same load like:
Period:
LOADCal.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$]);
thank you Tresesco