Week on Week & Previous Year Calculations

[alwayslearning]

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.

[sunny_talwar · Accepted solution]

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]

Thanks Sunny, that worked great.