Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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.
i can't try it but may be this
Sum({1<Year={2017}>}COSTPO)
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/
I cannot use alternate states as that is an extension and I am not authorized to use any extensions for Qlik at this time.
if that is the case then the only option I can think of is
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?
I think you don't need alternate states, rather uses set identifier 1 to use the full set of records in your application.
How would I do that? Sorry, still very new to Qlik