Master Calendar question dealing with dates

Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

I have the following Master Calendar that has been working fine until now:

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;

The challenge I now face is dealing with date formats outside the United States. We store the dates in the Database like it would display in our clients environment. The problem with this is when I run it through the event calendar above a date store as 12/6/2014 which is actually 6/12/2014 is converted to a fiscal year of 2014/2015 when it is actually 2013/2014. I hope this makes sense. Please let me know and I will try to explain it differently if need be.

Thanks

David

Re: Master Calendar question dealing with dates

sunny_talwar — Wed, 27 Apr 2016 18:19:52 GMT

You can use Alt() function to read the different formats. I guess one format needs to be a primary format which will go first in the Alt function.

Alternatively, if you can flag US data as 1 and outside data as 2 then you can use a if statement while loading the dates.

topic Re: Master Calendar question dealing with dates in QlikView

Master Calendar question dealing with dates

Re: Master Calendar question dealing with dates