Hello Community,
I have a chart that I want to display certain Quarter periods back based upon the user's selection of the Quarter field. So, for example, if the user selected 2016 Q3, Column1 would display the current quarter (i.e., "2016 Q3"), and Column2 would display 1 quarter prior ("2016 Q2"), Column3 would display 2 quarters prior ("2016 Q1") and so forth. My question is what expression to write in the labels input box to get it to do that.
Quarter is string/text values, which complicates matters. If it's values were numeric (like Year), I would simply use the Max or GetFieldSelections functions (e.g., to get 2 years back, I would use either =Max(Year)-2 or =GetFieldSelections(Year)-2). I thought of using the MaxString function, but it doesn't have the optional rank/offset function (like Max does), so it won't work for my purposes. I read in another post about Above (and Above TOTAL), and attempted combining it with the other functions mentioned earlier, but I couldn't get it to work properly.
I also tried creating an index of the Quarter field, essentially by loading its distinct values into a new table, sorting them, and creating an index (see the script below). My thought was that I could use that index to point to the relevant Quarter value, either in an IF statement or a set expression. (e.g., first creating a variable representing 1 quarter prior [v1Qprior = Qkey-1], then in the chart label using an expression like "if(Qkey=v1Qprior),Quarter"), but I haven't gotten that to work either. Perhaps I'm using the wrong syntax.
I'm stuck. Any advice or suggestions you can give would be greatly appreciated.
Thanks,
-John
QuarterTable:
Load Distinct
Quarter,
AutoNumber(Quarter) AS QKey
Resident FactSource
Order By Quarter asc;
