Week on Week & Previous Year Calculations

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

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.

Re: Week on Week & Previous Year Calculations

sunny_talwar — Wed, 29 Nov 2017 17:25:45 GMT

How about this

=Sum({<Year={$(=Max(Year))}, FWeekNo={$(=Max({<Year={$(=Max(Year))}>}FWeekNo))}>} Value)

So, basically look for Max of FWeekNo within the Max Year.....

alwayslearning — Thu, 30 Nov 2017 15:54:41 GMT

Thanks Sunny, that worked great.