Rolling Fiscal Year over Year

Add to your relative calendar:


CASE
WHEN (DATEDIFF(DAY, C2.FYStartDate, C.FYStartDate) / 28) BETWEEN -12 AND 0                 Then 'This Year'
WHEN (DATEDIFF(DAY, C2.FYStartDate, C.FYStartDate) / 28) BETWEEN -24 AND -13
Then 'Last Year' ELSE NULL End AS [RollingFiscalYear]

Sort:

Match(Cal.WeekMon
,Dual($(=Only({<FullDate = {'$(=Date(Today()))'}>}Cal.WeekMon)),1)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-7))'}>}Cal.WeekMon)),2)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-14))'}>}Cal.WeekMon)),3)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-21))'}>}Cal.WeekMon)),4)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-28))'}>}Cal.WeekMon)),5)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-35))'}>}Cal.WeekMon)),6)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-42))'}>}Cal.WeekMon)),7)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-49))'}>}Cal.WeekMon)),8)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-56))'}>}Cal.WeekMon)),9)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-63))'}>}Cal.WeekMon)),10)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-70))'}>}Cal.WeekMon)),11)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-77))'}>}Cal.WeekMon)),12)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-84))'}>}Cal.WeekMon)),13)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-91))'}>}Cal.WeekMon)),14)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-98))'}>}Cal.WeekMon)),15)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-105))'}>}Cal.WeekMon)),16)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-112))'}>}Cal.WeekMon)),17)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-119))'}>}Cal.WeekMon)),18)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-126))'}>}Cal.WeekMon)),19)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-133))'}>}Cal.WeekMon)),20)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-140))'}>}Cal.WeekMon)),21)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-147))'}>}Cal.WeekMon)),22)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-154))'}>}Cal.WeekMon)),23)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-161))'}>}Cal.WeekMon)),24)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-168))'}>}Cal.WeekMon)),25)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-175))'}>}Cal.WeekMon)),26)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-182))'}>}Cal.WeekMon)),27)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-189))'}>}Cal.WeekMon)),28)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-196))'}>}Cal.WeekMon)),29)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-203))'}>}Cal.WeekMon)),30)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-210))'}>}Cal.WeekMon)),31)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-217))'}>}Cal.WeekMon)),32)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-224))'}>}Cal.WeekMon)),33)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-231))'}>}Cal.WeekMon)),34)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-238))'}>}Cal.WeekMon)),35)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-245))'}>}Cal.WeekMon)),36)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-252))'}>}Cal.WeekMon)),37)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-259))'}>}Cal.WeekMon)),38)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-266))'}>}Cal.WeekMon)),39)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-273))'}>}Cal.WeekMon)),40)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-280))'}>}Cal.WeekMon)),41)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-287))'}>}Cal.WeekMon)),42)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-294))'}>}Cal.WeekMon)),43)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-301))'}>}Cal.WeekMon)),44)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-308))'}>}Cal.WeekMon)),45)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-315))'}>}Cal.WeekMon)),46)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-322))'}>}Cal.WeekMon)),47)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-329))'}>}Cal.WeekMon)),48)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-336))'}>}Cal.WeekMon)),49)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-343))'}>}Cal.WeekMon)),50)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-350))'}>}Cal.WeekMon)),51)
,Dual($(=Only({<FullDate = {'$(=Date(Today()-357))'}>}Cal.WeekMon)),52)
)

Peek on load variable

Getting full fiscal quarters via their start date:


Start:
LOAD Distinct
Date(Floor(FYStartDate),'YYYY-MM-DD') AS FYStartDate
;
SQL
Select Distinct
Min(FYStartDate) AS FYStartDate
From REF.Calendar
Where Year(FullDate) = Year(DATEADD(mm,-12,GETDATE()))
AND FYQuarter = '1'
;

LET vStartDate = peek('FYStartDate');

Drop Table Start;

Current Week OnOpen Macro

so I don't forget...


Sub OnOpen

InitialSelections

End Sub

Sub InitialSelections
ActiveDocument.Fields("Cal.RelativeWeek").select "0",True
ActiveDocument.Fields("Cal.WeekStartMon").selectpossible
ActiveDocument.Fields("Cal.RelativeWeek").clear
End Sub

Syntax for Alternate States:

ActiveDocument.GetField("CUSTOMERS", "State1")

GeoAnalytics points...

City state is "Location Named Point"

'[-118.243685,34.052234]' is "Point"

Long Lat in separate fields is "Latitude and Longitude Point"