Set Analysis with Master Calendar

sungyunley — Fri, 15 Jan 2021 23:20:22 GMT

Hello!

I want to use set analysis to sum sales QTD, and YTD. I did some research, and found that most folks can achieve this by using a $ expression with YEAR set to TODAY(), i've also seen folks using MAX(Year Sequence Number). The reason i cannot use YEAR set to TODAY() is because our companies fiscal year begins on Feb 1 vs Jan 1, so if the close date of an oppty landed on Jan 2nd 2020, the fiscal year returned should be FY19 opposed to FY20. I am also unable to use MAX(Year Sequence) because our master cal has dates through 2030, so the max year number would be for 2030 instead of the current year.

I was thinking i can use the fiscal year sequence number, if i can tie it back to today's date. I'd appreciate any other ideas!

Re: Set Analysis with Master Calendar

GaryGiles — Sat, 16 Jan 2021 12:01:22 GMT

The YearStart function allows you to specify a starting month offset. For a Fiscal Year start of February 1st, try this:

=Year(YearStart(Today(),0,2))

The third parameter, 2, specifies February as the starting month.

topic Re: Set Analysis with Master Calendar in App Development

Set Analysis with Master Calendar

Re: Set Analysis with Master Calendar