9 Replies Latest reply on Aug 16, 2018 2:26 PM by swaroop.gantela

Fixing to the previous fiscal year

Hi all,

First, thank you all for engaging, I am on these threads daily.  I'm using 2018.1 and a sample workbook is attached.

As for my question, I am trying to label my regions based on a SUM of the previous fiscal year's sales (size A, B, C or D).

Ideally this is dynamic so I don't have to update it after we change fiscal years.  FY is August through July.

My thought is

1- Create a field [Previous FY Sales] that isolates the previous fiscal year's sales, using something like this below, but dynamic.  THIS IS THE PART I NEED HELP WITH.

SUM({ FIXED [Region] :

SUM( {FIXED [Region], [Order Date] >= DATE('8/1/2016')

AND

[Order Date] <= DATE('7/31/2017'): SUM([Sales])}) })

2- Use the newly created [Previous FY Sales] create a new label field [Current Region Size], like this

IF [Previous FY Sales] <=400000 then "A"

ELSEIF [Previous FY Sales] <=600000 then "B"

ELSEIF [Previous FY Sales] <=700000 then "C"

ELSE "D"

END

Thank you

• 1. Re: Fixing to the previous fiscal year

Kevin, I did this in 2 steps so you can see the logic but you could combine it.

I copied your LOD calculation and modified that one.

First step: Create 2 new date fields: Start and End dates for the Previous FY.  These are dynamic: they create a date by manipulating the YEAR portion of the date to be relative to the YEAR of today's date.

Second step: use those 2 fields in your LOD calc instead of the fixed date calcs you used.

Ask more questions if you need to.

• 2. Re: Fixing to the previous fiscal year

Thanks Michael,

The function is summing all times, even outside of the previous fiscal year.  The result I'm looking for is ~\$147,000 (last 5 months of '17 plus first 7 months of '18).

• 3. Re: Fixing to the previous fiscal year

Hi Kevin,

Something I would suggest is using LOD only when necessary as it can slow your dashboard's performance as it goes against the database rather than within the viz.

A common calculation I do is to convert the date to "FY Start" so I can use accordingly:

You could use this to create the FY label (eg. "FY18"):

You could then use these on your viz if desired and then (finally) use table calcs instead of a LOD expression, such as:

Of course, all this depends on your use case, just wanted to make sure you needed to use a FIX LOD expression.

Hope this helps.

Best,

Bryce

• 4. Re: Fixing to the previous fiscal year

Thanks Bryce and Micheal.

I used your FY labels to create a new field here:

SUM(IIF ([Fiscal Year Label] = "FY17",[Sales],0))

Of course, this is not dynamic, but it does give me a field to group using IF/THEN.

• 5. Re: Fixing to the previous fiscal year

Kevin and Bryce,

Apologies for jumping in the middle.

I may have overlooked this in the thread,

but is it possible to instead change the default settings

for the fiscal year start of your date?

• 6. Re: Fixing to the previous fiscal year

I think this is useful, but, correct me if i'm wrong, you're unable to actually use the FY in calculations - it's simply a display format. Eg. If you then wanted to use it in a LOD expression, you'd actually be using the calendar year. Is that right?

• 7. Re: Fixing to the previous fiscal year

Bryce,

I will admit at the outset that I haven't fully looked into the details of the questions

posed in this thread. It is very likely that I have oversimplified the approach.

With regards to FY and calculations, it does look like there will be problems using

them in some types of calculations, as stated in the online help:

"Date functions do not take account of the configured fiscal year start."

Fiscal Dates

I'm not completely sure how it interacts with LODs.

I ran a very cursory test of [Year Total Sales]:

{ FIXED [Region],[Order Date (Years)]:SUM(Sales)}

with [OrderDate (Years)] a custom date created from [Order Years]

that had it's default start set to August.

It appeared that it generated the same values as were posted above

(screenshot below).

For the question at hand, I was using table calculations and it seemed

to work with the FYs.

But again, there are very likely subtleties that I have overlooked.

• 8. Re: Fixing to the previous fiscal year

Thank you both Bryce and Swaroop,

This is actually helpful.  Also, I like the black screen and am curious how you did that.

Thanks again

Kevin

• 9. Re: Fixing to the previous fiscal year

Kevin,