Master Calendar for Multiple Clients with different Fiscal Years

patbuchanan — Wed, 25 Nov 2020 16:16:04 GMT

I've used some code from the community before and tweaked it when loading one client to pull in their custom Fiscal Year. I ended up using Peek to fill in their Fiscal Year start month and the code works when I was just working with one client with one record from my FSFYr table.

Now I've been asked to do the same with my app that contains all clients but they all don't have the same Fiscal Start Month so when I use Peek it just uses the 'FSFiscalMonth' for the first client for all clients instead of peeking for each client (AcctNum) to grab their unique start month. I've also tried ApplyMap but it did just always uses the first client used like the Peek command. Anyone solved this before?

LET vToday = Num(today());

LET vFM = Peek('FSFiscalMonth',0,'FSFYr');

//Set vFM = 9; // First month of fiscal year

Set vFD = 4; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)

MasterCalendar:

Load Distinct

Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year ex. 2015/16

Dual(Month, fMonth) as FMonth, // Dual fiscal month

Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,

Ceil(([EventStartDate]-StartOfFWeekOne+1)/7) as FWeekNo,

Load Year + If(Month>=$(vFM), 1, 0) as fYear, // Numeric fiscal year

Mod(Month-$(vFM), 12)+1 as fMonth, // Numeric fiscal month

Dual('Q' & Ceil(Month/3), Ceil(Month/3)) as Quarter,

WeekStart(FYearStart,0,$(vFD)) as StartOfFWeekOne,

Load

[EventStartDate],

YearStart([EventStartDate],0,$(vFM)) as FYearStart,

Year([EventStartDate]) as Year,

Month([EventStartDate]) as Month,

Date(Monthstart([EventStartDate]), 'MMM-YYYY') as MonthYear,

Week([EventStartDate]) as ISOWeekNo,

Dual(WeekDay([EventStartDate]),Mod(WeekDay([EventStartDate]-$(vFD)),7)+1) as WeekDay,

Day([EventStartDate]) as Day,

Date([EventStartDate], 'MM/DD') as DATEMMDD

Resident Events;

topic Master Calendar for Multiple Clients with different Fiscal Years in QlikView

Master Calendar for Multiple Clients with different Fiscal Years