Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
nezuko_kamado
Creator
Creator

MaxString()-1 not working, then how to select the previous(the second) maxstring()?

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.

nezuko_kamado_0-1623170164090.png

 

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]

 

Labels (2)
1 Solution

Accepted Solutions
Sammy_AK
Creator II
Creator II

try this:

Avg({<FiscalYear = {'$(=FieldValue('FiscalYear',$(=FieldIndex('FiscalYear',MaxString(FiscalYear))-1)))'}>}revenue)

View solution in original post

2 Replies
Sammy_AK
Creator II
Creator II

try this:

Avg({<FiscalYear = {'$(=FieldValue('FiscalYear',$(=FieldIndex('FiscalYear',MaxString(FiscalYear))-1)))'}>}revenue)

marcus_sommer

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