Dynamic Column Titles using indexes

[victagbc]

I am building a report table that lists revenue data for the past 4 quarters each in a column.  The quarters have a field which holds their name as ROL_QTR_YEAR, but in my report I am referencing them by their index field which is Quarter_Number.

Instead of having the Quarter number appearing as the column Title, I would prefer to have the ROL_QTR_YEAR displayed, but when I try =getcurrentselections(ROL_QTR_YEAR,',') it does not seem to work and just gives me a -.  Please guide me on how to make this work dynamically so I can avoid manually assigning titles to the columns that have meaning to other people.

[GaryGiles · Accepted solution]

I'm not completely clear on your setup, but you could try some variation of this:

pick(1,$(=concat(distinct chr(39)&ROL_QTR_YEAR&chr(39),',',Quarter_Number)))

pick(2,$(=concat(distinct chr(39)&ROL_QTR_YEAR&chr(39),',',Quarter_Number)))

. . . 

Is this being used in a table or pivot?

[victagbc]

Thanks,

Using a column of data in my table as an example, the data query I use is 

Sum({<SellingDay={'>=$(=vSellingDayLower)<=$(=vSellingDayUpper)'},[Quarter Number]={'8'}>}Revenue)

...but for the title of column...when I try ...

pick(8,$(=concat(distinct chr(39)&ROL_QTR_YEAR&chr(39),',',Quarter_Number))) 

I get nothing returned.  I noticed it gives me the correct value for the first column only 

Then I tried

pick(1,$(=concat(distinct chr(39)&ROL_QTR_YEAR&chr(39),',',Quarter_Number))) 

it seems to work and populates the header with a quarter name, but only returns the value for quarter 8.

When I try pick(2   or pick(3  , etc they return nothing?

[victagbc]

Attachment shows headers as example.

[GaryGiles]

Is this in a pivot or a table?  Are there any active selections?  How many Quarter_Number s do you have?  Are they numeric or alphanumeric?  The Quarter_Number at the end of the function is meant to sort the quarters. 

pick(1,$(=concat({1} distinct Total chr(39)&ROL_QTR_YEAR&chr(39),',',Quarter_Number))) 

Also, create a separate table and put this in the measure: 

=concat({1} distinct Total chr(39)&ROL_QTR_YEAR&chr(39),',',Quarter_Number)

What do you see?

[victagbc]

It is in a table.  there is one Quarter_Number per quarter and the number of quarters grows each quarter, but only the current quarter (Quarter_Number=9)  and previous 8 quarters are numbered with a Quarter _Number index.  The others appear to have  a blank Quarter_Number which shows as - in my table.

Here is the output I got in the separate table for =concat({1} distinct Total chr(39)&ROL_QTR_YEAR&chr(39),',',Quarter_Number)

'Q1-2018','Q1-2019','Q1-2020','Q1-2021','Q2-2018','Q2-2019','Q2-2020','Q2-2021','Q3-2018','Q3-2019','Q3-2020','Q3-2021','Q4-2017','Q4-2018','Q4-2019','Q4-2020','Q4-2021'

[victagbc]

thanks to you , I have it working now.!