Hi, I am looking to calculate some data:
I want to sum :
current week
previous week
same week last year.
YTD and YTD Previous Year
I am using a fiscal master calendar, my issue seems to be when I do Max(fWeekNo) it is always 53 rather than max of the current year.
Set vFM = 9 ; // First month of fiscal year
Set vFD = 0; // First Day of the week (0=Mon, 1=Tue, ... , 6=Sun)
MasterCalendar:
Load distinct
Dual(fYear-1 &'/'& fYear, fYear) as FYear, // Dual fiscal year
Dual(Month, fMonth) as FMonth, // Dual fiscal month
Dual('Q' & Ceil(fMonth/3), Ceil(fMonth/3)) as FQuarter,
Ceil(([Value]-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
[Value],
YearStart([Value],0,$(vFM)) as FYearStart,
Year([Value]) as Year,
Month([Value]) as Month,
Date(Monthstart([Value]), 'MMM-YYYY') as MonthYear,
Week([Value]) as ISOWeekNo,
Dual(WeekDay([Value]),Mod(WeekDay([Value]-$(vFD)),7)+1) as WeekDay,
Day([Value]) as Day,
Date([Value], 'MM/DD') as DATEMMDD
Resident Table;
The Set Analysis I have is as follows for current week:
=Sum({< Year={$(=Max(Year))}, FWeekNo={$(=Max(FWeekNo))}>} Value) - Issue here seems to be That Max(FWeekNo) gives 53 rather than current week.
Hope someone can help we complete this.
