topic Re: Current and Prior Month from Variable? in QlikView

Current and Prior Month from Variable?

carlcimino — Mon, 08 Oct 2018 17:28:52 GMT

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;

Re: Current and Prior Month from Variable?

dplr-rn — Mon, 08 Oct 2018 18:21:30 GMT

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

Re: Current and Prior Month from Variable?

carlcimino — Mon, 08 Oct 2018 20:08:16 GMT

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

Re: Current and Prior Month from Variable?

dplr-rn — Mon, 08 Oct 2018 21:02:07 GMT

Use

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

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

hope i understood correctly

Re: Current and Prior Month from Variable?

carlcimino — Tue, 09 Oct 2018 15:32:47 GMT

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.

Re: Current and Prior Month from Variable?

dplr-rn — Tue, 09 Oct 2018 15:41:37 GMT

"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

Re: Current and Prior Month from Variable?

carlcimino — Tue, 09 Oct 2018 16:11:48 GMT

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.