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.

[ahaahaaha]

Hi Casey,

May be like this

Expresssion1 (Current Fiscal Year)

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

Expression2 (Last Fiscal Year)

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

Expression3 (Average of the previous three years

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

Regards,

Andrey

[#3]

These will still change based upon whatever filters I select, correct? I'm looking to have one table show the CurrentFY, LastFY, and 3YAvgFY regardless of what filters are selected.

[zebhashmi · Accepted solution]

i can't try it but may be this

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

[vamsee]

Use the expressions the above

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

and instead of 1 use Time1 or Time2 by creating alternate states.

QlikView Alternate States Use-Cases

http://www.qlikfix.com/2014/08/12/alternate-states/

[#6]

I cannot use alternate states as that is an extension and I am not authorized to use any extensions for Qlik at this time.

[vamsee]

if that is the case then the only option I can think of is

https://qliktech.hosted.jivesoftware.com/thread/237894

[#8]

Jahanzeb's suggestion worked. However, I am using the rangesum function as well since I am showing cumulative totals. My expressions look as such:

Current Fiscal Year:

rangesum(above(Sum({1<STATUS_GROUP={'Closed'}, CFYear={'2016/2017'}>}COSTPO),0,12))

Last Fiscal Year:

rangesum(above(Sum({1<STATUS_GROUP={'Closed'}, CFYear={'2015/2016'}>}COSTPO),0,12))

However, it doesn't work when I attempt to do an average for all three fiscal years. I am trying to use this expression:

rangeavg(above(sum({1<STATUS_GROUP={'Closed'}, CFYear={'2014/2015,2015/2016,2016/2017'}>}COSTPO),0,12))

What expression would work for the cumulative 3 year average?

In addition, these are hardcoded expressions. How would I change them so that it will automatically update to the newest current fiscal year, and then the subsequent previous and 3 year average?

[agigliotti]

I think you don't need alternate states, rather uses set identifier 1 to use the full set of records in your application.

[#10]

How would I do that? Sorry, still very new to Qlik