Discussion Board for collaboration related to QlikView App Development.
Hello, I'm using a Autogenerate Calendar and trying to get the current month and prior year month flags working. My script is below. I tried the suggestion marked correct from Getting Previous month date from current month date but got an invalid expression when I substituted my date field. Any help would be appreciated.
//Find max report year/month
Cal_Min_Max:
Load
max(DATE(MakeDate(LEFT(AS_OF_YYYYMM,4),'01',RIGHT(AS_OF_YYYYMM,2)),'DD/MM/YYYY')) as MaxCalDate,
min(DATE(MakeDate(LEFT(AS_OF_YYYYMM,4),'01',RIGHT(AS_OF_YYYYMM,2)),'DD/MM/YYYY')) as MinCalDate
FROM [lib://PRS_Auto_Inforce_QVD/AUTO_INFORCE.qvd]
(qvd);
//Store min and max inforce to a Variable
Let vMax_Inforce_Date = num(peek('MaxCalDate',0,'Cal_Min_Max'));
Let vMin_Inforce_Date = num(peek('MinCalDate',0,'Cal_Min_Max'));
drop table Cal_Min_Max;
Temp_Calendar:
LOAD
$(vMin_Inforce_Date) + Iterno()-1 As Num,
Date($(vMin_Inforce_Date) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(vMin_Inforce_Date) + IterNo() -1 <= $(vMax_Inforce_Date);
MasterCalendar:
Load
TempDate AS OrderDate,
Month(TempDate) As Month,
YeartoDate(TempDate)*-1 as CurYTDFlag,
Date( MonthEnd(addmonths(Max(Date#(TempDate ,'DD-MMM-YYYY')),-1)),'DD-MMM-YYYY') as PRIOR_YEAR_MONTH,
YeartoDate(TempDate,-1)*-1 as LastYTDFlag
Resident Temp_Calendar
Order By TempDate ASC;
Drop Table Temp_Calendar;
Few things
InYearToDate(CalDate, '$(vToday)', 0) * -1 as CYTDFlag,
InYearToDate(CalDate, '$(vToday)', -1) * -1 as LYTDFlag,
Hi Dilip, Thanks for responding. I used the better calendar scripts from Rob W. Works great! Current month is not the month of today it is the month of the max INFORCE_DATE data in my fact set. Here's my new calendar script that is working. I just need to get the CM flag and a same month last year flag.
MasterCalendar:
Load
TempDate AS INFORCE_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('INFORCE_DATE', recno()))-1 as mindate,
max(FieldValue('INFORCE_DATE', recno())) as maxdate
AUTOGENERATE FieldValueCount('INFORCE_DATE');
Use
InMonthToDate (TempDate, maxdate, 0)*-1 as CM_Flag
InMonthToDate (TempDate, maxdate, -12)*-1 as CM_PY_Flag
hope i understood correctly
Hi those added the flags correctly and the calendar works. Those flags will work for a static view but. Now if I want to have KPIs that show Current Month and Prior Year Month and be flexible based on the INFORCE_DATE selected. I have to do something else. What I have tried is below.
I added the flags as suggested. I also did a where exists because my data only has 1 inforce date so I don't need all the dates between each snapshot in my calendar.
CALENDAR:
Load
TempDate AS INFORCE_DATE,
week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
InMonthToDate (TempDate, maxdate, 0)*-1 as CM_Flag,
InMonthToDate (TempDate, maxdate, -12)*-1 as CM_PY_Flag,
WeekDay(TempDate) as WeekDay
where exists (INFORCE_DATE,TempDate)
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('INFORCE_DATE', recno()))-1 as mindate,
max(FieldValue('INFORCE_DATE', recno())) as maxdate
AUTOGENERATE FieldValueCount('INFORCE_DATE');
store CALENDAR into 'lib://PRS_Auto_Inforce_QVD/CALENDAR.qvd' (qvd);
drop table CALENDAR;
Using this it returns the correct value (left below with number erased):
Sum({<AS_OF_YYYYMM={$(=max(AS_OF_YYYYMM))}>}AUTO_ANNUAL_PREMIUM)
Using this it returns -.
Sum({<INFORCE_DATE={$(=(max(INFORCE_DATE)))}>}AUTO_ANNUAL_PREMIUM)
I was hoping to use the second expression and do -1 to get the prior date.
"Using this it returns -.
Sum({<INFORCE_DATE={$(=(max(INFORCE_DATE)))}>}AUTO_ANNUAL_PREMIUM)
I was hoping to use the second expression and do -1 to get the prior date."
Sure that should work usually but if all date values are not present in the master calendar not sure how you can know which is the prior date
If the calendar only has month dates in it then why wouldn't Sum({<INFORCE_DATE={$(=(max(INFORCE_DATE)))}>}AUTO_ANNUAL_PREMIUM) work? If I do Sum({<AS_OF_YYYYMM={$(=(max(AS_OF_YYYYMM)))}>}AUTO_ANNUAL_PREMIUM) it works. They are 1:1 in the data model.