Current and Prior Month from Variable?

[carlcimino]

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; 

[dplr-rn]

Few things

• The link you mentioned seem to be using the expression for front end not in script
  • therefore in script Max is not needed
  • also check the format that you have data in. your data seems to be in 'DD/MM/YYYY' according to top part of your script. so you will need to modify that
• in Prior_Year_Month - please explain what do you want there. i can try and help accordingly
• if its just LYTD and CYTD flag use below code (vToday is variable with current day)

  • InYearToDate(CalDate, '$(vToday)', 0) * -1 as CYTDFlag,

  •    InYearToDate(CalDate, '$(vToday)', -1) * -1 as LYTDFlag,

• In general i would recommend using script from below link for Master calendar. the link describes why
  • Better Calendar Scripts | Qlikview Cookbook

[carlcimino]

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');

[dplr-rn]

Use

InMonthToDate (TempDate, maxdate, 0)*-1 as CM_Flag

InMonthToDate (TempDate, maxdate, -12)*-1 as CM_PY_Flag

hope i understood correctly

[carlcimino]

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.

[dplr-rn]

"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

[carlcimino]

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.