Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I tired to average previous fiscal year revenue when selecting current fiscal year with MaxString() in order to add previous FiscalYear average line to a Bar chart. When retrieving string orders using MaxString(value) -1, which doesn't bring the previous order.
I set the order the FiscalYear SET FiscalYear='FiscalYear2017;FiscalYear2018;FiscalYear2019';
but MaxString(value) -1 doesn't bring the previous fiscal year.
Is there any way I can average the previous fiscal year revenue when selecting current fiscal year? For example, if I select FiscalYear 2018, a reference line is the average of 2017.
Dat:
load *inline [ FiscalYear, Location, revenue
FiscalYear2017, A, 30
FiscalYear2017, B, 45
FiscalYear2017, C, 33
FiscalYear2017, D, 55
FiscalYear2018, A, 45
FiscalYear2018, B, 36
FiscalYear2018, C, 49
FiscalYear2018, D, 70
FiscalYear2019, A, 44
FiscalYear2019, B, 73
FiscalYear2019, C, 45
FiscalYear2019, D, 32]
try this:
Avg({<FiscalYear = {'$(=FieldValue('FiscalYear',$(=FieldIndex('FiscalYear',MaxString(FiscalYear))-1)))'}>}revenue)
try this:
Avg({<FiscalYear = {'$(=FieldValue('FiscalYear',$(=FieldIndex('FiscalYear',MaxString(FiscalYear))-1)))'}>}revenue)
Don't use strings or formatted values if you want to calculate and/or to match them in any way else use always purely numbers. In your case you may add another field [FiscalYearNum] which contains 2017, 2018, 2019, ... and/or you used dual() to get a string- and a numeric-value.
Doing this doesn't mean that you couldn't use such strings within the UI to show them as dimensions or to select any values. The needed fields must be just related to each other within a master-calendar.
Of course most things could be also done with strings or formatted values and more or less complex expressions but those efforts are much higher as doing it already within the script.
- Marcus