topic Re: Dynamic Column Titles using indexes in App Development

Dynamic Column Titles using indexes

victagbc — Sat, 16 Nov 2024 17:32:44 GMT

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.

Re: Dynamic Column Titles using indexes

GaryGiles — Mon, 01 Feb 2021 18:08:23 GMT

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?

Re: Dynamic Column Titles using indexes

victagbc — Mon, 01 Feb 2021 18:21:38 GMT

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?

Re: Dynamic Column Titles using indexes

victagbc — Mon, 01 Feb 2021 18:22:40 GMT

Attachment shows headers as example.

Re: Dynamic Column Titles using indexes

GaryGiles — Mon, 01 Feb 2021 21:41:38 GMT

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?

Re: Dynamic Column Titles using indexes

victagbc — Mon, 01 Feb 2021 22:41:13 GMT

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'

Re: Dynamic Column Titles using indexes

victagbc — Mon, 01 Feb 2021 22:56:20 GMT

thanks to you , I have it working now.!