topic Re: Last Business Day in QlikView

Last Business Day

tmumaw — Thu, 06 Oct 2016 12:08:16 GMT

Hi everyone,

I am trying to figure the last business day of every month. We work on a 4-4-5 calendar. Any idea? Here s the script I am running to get my dates. Thanks

CurrentDate:
LOAD FISPD,
FISYR,
[Billing Date],
WEDAT,
WKNO
FROM (qvd)
where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');
LET vFisPd = peek('FISPD', 0, 'CurrentDate');
LET vWkNo = peek('WKNO', 0, 'CurrentDate');

DROP TABLE CurrentDate;

FiscalCalendar:
LOAD FISPD,
FISYR,
[Billing Date] as [Current Date],
[Billing Date] as [RunDate],
WEDAT as [Week Ending],
WKNO,
WeekDay([Billing Date]) as Weekday,
If(WeekDay([Billing Date]) = 'Sat',1,0) as Wkend,
Day(MonthEnd([Billing Date])) AS DaysInMonth,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS MTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO = '$(vWkNo)', 1) AS WTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO = '$(vWkNo)' -2, 1) AS L2WTDFlag,
if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO = '$(vWkNo)' -1, 1) AS L1WTDFlag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling5)', 1) AS RTD5Flag,
if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling2)', 1) AS RTD2Flag,
if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling1)', 1) AS RTD1Flag
FROM (qvd)
WHERE [Billing Date] >= '$(vHistoryDate)' AND [Billing Date] <= '$(vToday)';

Re: Last Business Day

marcus_sommer — Thu, 06 Oct 2016 12:27:17 GMT

Do you use a master-calendar like this one: Recipe for a 4-4-5 Calendar or 4-5-4 Master Calendar?

- Marcus

Re: Last Business Day

tmumaw — Thu, 06 Oct 2016 12:55:54 GMT

My calendar is already broken down to 4-4-5. I need a way within my script to set a flag telling me it's the last day of my fiscal period (FISPD) in the above script.

Re: Last Business Day

marcus_sommer — Thu, 06 Oct 2016 13:56:26 GMT

The month are fixed so that an approach like:

-match(DayCounter, 28, 56, 91, ...) as MonthEndFlag

DayCounter is a running Counter within master-calendar.

- Marcus

Re: Last Business Day

tmumaw — Mon, 10 Oct 2016 11:44:33 GMT

I got it to work. Here is what I did.

CurrentDate:

LOAD FISPD,

FISYR,

[Billing Date],

WEDAT,

WKNO

FROM (qvd)

where [Billing Date] = '$(vToday)';

LET vFisYr = peek('FISYR', 0, 'CurrentDate');

LET vFisPd = peek('FISPD', 0, 'CurrentDate');

LET vWkNo = peek('WKNO', 0, 'CurrentDate');

DROP TABLE CurrentDate;

FiscalCalendar_Temp:

LOAD FISPD,

FISYR,

FISPD & FISYR as PerYr,

[Billing Date] as [Current Date],

Num([Billing Date]) as NumCurrDate,

[Billing Date] as [RunDate],

WEDAT as [Week Ending],

WKNO,

WeekDay([Billing Date]) as Weekday,

If(WeekDay([Billing Date]) = 'Sat',1,0) as Wkend,

Day(MonthEnd([Billing Date])) AS DaysInMonth,

if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS YTDFlag,

if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO <= '$(vWkNo)', 1) AS MTDFlag,

if([Billing Date] <= '$(vToday)' AND FISPD = '$(vFisPd)' AND WKNO = '$(vWkNo)', 1) AS WTDFlag,

if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO = '$(vWkNo)' -2, 1) AS L2WTDFlag,

if([Billing Date] <= '$(vToday)' AND FISPD <= '$(vFisPd)' AND WKNO = '$(vWkNo)' -1, 1) AS L1WTDFlag,

if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling5)', 1) AS RTD5Flag,

if([Billing Date] <= '$(vPrevToday)' AND [Billing Date] >= '$(vRolling4)', 1) AS RTD4Flag,

if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling3)', 1) AS RTD3Flag,

if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling2)', 1) AS RTD2Flag,

if([Billing Date] <= '$(vToday)' AND [Billing Date] >= '$(vRolling1)', 1) AS RTD1Flag

FROM (qvd)

WHERE [Billing Date] >= '$(vHistoryDate)' AND [Billing Date] <= '$(vToday)';

BigDate:

Load

FISYR as FISYRX,

FISPD as FISPDX,

max([Current Date]) as LastDay

Resident FiscalCalendar_Temp group by FISYR, FISPD

;

Left Join (FiscalCalendar_Temp)

Load *,

FISPDX & FISYRX as PerYr,

Date(LastDay,'YYYY-MM-DD') as LastDayMth,

Num(LastDay) as NumLastDayMth

Resident BigDate

;

Drop Table BigDate

;

Drop Fields FISPDX, FISYRX, LastDay;

FiscalCalendar:

Load *,

if(NumCurrDate = NumLastDayMth,1,0) as MthEnd

Resident FiscalCalendar_Temp

;

Drop Table FiscalCalendar_Temp

;