Line Chart showing CY, Last Year, and 3 Year Average (Fiscal Year)

[#1]

I have a table "WR_OTHER" which lhas a field called "COSTPO". I have created a line chart which has a dimension called "DATE.autocalendar.Month" to show out the months, with an expression to sort by fiscal year. The "DATE.autocalendar.Month" is pulled from a table called "OPS_DATES" which is basically a table I'm using for my master calendar.

I need this chart to show only the Current Fiscal Year, the Last Fiscal Year, and an average of the previous three years, regardless of what filters are set.

The only date field in the "WR_OTHER" table is called "DATE_USED" and lists out the date as mm/dd/yyyy.

Any help would be appreciated!

EDIT: It also needs to be cumulative.

[agigliotti]

use the expressions as Andrey told you above adding set identifier 1 as below:

Expresssion1 (Current Fiscal Year)

Sum({1<Year={2017}>}COSTPO)

Expression2 (Last Fiscal Year)

Sum({1<Year={2016}>}COSTPO)

Expression3 (Average of the previous three years

Avg({1<Year={2014, 2015, 2016}>}COSTPO)

[vamsee]

instead of 2014,2015,2016 use these

Create three Variables

     Let vCurrentYear=Year(today());

     Let vPriorYear=Year(Today())-1;

     Let vPrevYear= Year(Today())-2;

     .

     .

     .

Eg:     Sum({<Year={'$(vCurrentYear)'}>} Values)

[#4]

Wouldn't that give me calendar year and not my fiscal year? My Fiscal Year is July 1 through June 30.

[vamsee]

Yes it would.

Instead of Year(Today()) use Year(Max(FiscalYear))

[#6]

Okay, I created CalYear variables using Year(today()) and FiscYear variables using (Year(Max(FiscalYear))

I tested it on my 2016/2017 cumulative sum expression using:

rangesum(above(Sum({1<STATUS_GROUP={'Closed'}, Year={'1(vCurrentFiscYear)'}>}COSTPO),0,12))

However, it still gives me a cumulative total of every year in my data set, not the current fiscal year.

Do I have to define "FiscalYear" in the script?

[zebhashmi]

*just an option to consider*

with fiscal year i would say make a table in script with fiscal year name, start and end.

and use intervalmatch to link

[#8]

What would the script syntax look like?