Hi, I have a dataset that has multiple columns for each of the 4 quarters in the year, along with a field that shows the current quarter. I am looking for a way to dynamically select the columns based on the Q# that is in the current quarter field. I was thinking that I could do a Dynamic Select and use a formula, but I am unsure of what formula to use to try and match a portion of the column name to the data in another column. Below and also in the attached file is my data, along with the view of the results that I am trying to achieve. Any suggestions on how this can be done? Thanks in advance! DATA: Activity ClassificationQ1 QTD-Current Qtr AvgQ1 QTD-Prior Qtr-AvgQ1 QTD-Total AvgQ2 QTD-Current Qtr AvgQ2 QTD-Prior Qtr-AvgQ2 QTD-Total AvgQ3 QTD-Current Qtr AvgQ3 QTD-Prior Qtr-AvgQ3 QTD-Total AvgQ4 QTD-Current Qtr AvgQ4 QTD-Prior Qtr-AvgQ4 QTD-Total AvgCurrent QtrActivity 1257[Null][Null][Null][Null][Null][Null][Null][Null][Null]Q4Activity 2134[Null][Null][Null][Null][Null][Null][Null][Null][Null]Q4Activity 34913[Null][Null][Null][Null][Null][Null][Null][Null][Null]Q4Activity 415520[Null][Null][Null][Null][Null][Null][Null][Null][Null]Q4Activity 1[Null][Null][Null]22628[Null][Null][Null][Null][Null][Null]Q4Activity 2[Null][Null][Null]73239[Null][Null][Null][Null][Null][Null]Q4Activity 3[Null][Null][Null]8816[Null][Null][Null][Null][Null][Null]Q4Activity 4[Null][Null][Null]88997[Null][Null][Null][Null][Null][Null]Q4Activity 5[Null][Null][Null]10111[Null][Null][Null][Null][Null][Null]Q4Activity 1[Null][Null][Null][Null][Null][Null]45348[Null][Null][Null]Q4Activity 2[Null][Null][Null][Null][Null][Null]92736[Null][Null][Null]Q4Activity 3[Null][Null][Null][Null][Null][Null]53338[Null][Null][Null]Q4Activity 4[Null][Null][Null][Null][Null][Null]251540[Null][Null][Null]Q4Activity 1[Null][Null][Null][Null][Null][Null][Null][Null][Null]8816Q4Activity 2[Null][Null][Null][Null][Null][Null][Null][Null][Null]19423Q4Activity 3[Null][Null][Null][Null][Null][Null][Null][Null][Null]49251Q4Activity 4[Null][Null][Null][Null][Null][Null][Null][Null][Null]331245Q4Activity 5[Null][Null][Null][Null][Null][Null][Null][Null][Null]181230Q4Activity 6[Null][Null][Null][Null][Null][Null][Null][Null][Null]42529Q4 RESULTS: If Current Qtr Field = Q2, only show the 3 fields that contain Q2 in the name Activity ClassificationQ2 QTD-Current Qtr AvgQ2 QTD-Prior Qtr-AvgQ2 QTD-Total Avg Activity 122628 Activity 273239 Activity 38816 Activity 488997 Activity 510111 If Current Qtr Field = Q4, only show the 3 fields that contain Q4 in the name Activity ClassificationQ4 QTD-Current Qtr AvgQ4 QTD-Prior Qtr-AvgQ4 QTD-Total Avg Activity 18816 Activity 219423 Activity 349251 Activity 4331245 Activity 5181230 Activity 642529 AYX Question-Dynamic Select-Data & Results.xlsx

Dynamic Select; Select columns that contain a match to data in another field

Hi,

I have a dataset that has multiple columns for each of the 4 quarters in the year, along with a field that shows the current quarter. I am looking for a way to dynamically select the columns based on the Q# that is in the current quarter field. I was thinking that I could do a Dynamic Select and use a formula, but I am unsure of what formula to use to try and match a portion of the column name to the data in another column. Below and also in the attached file is my data, along with the view of the results that I am trying to achieve. Any suggestions on how this can be done?

Thanks in advance!

DATA:

Activity Classification

Q1 QTD-Current Qtr Avg

Q1 QTD-Prior Qtr-Avg

Q1 QTD-Total Avg

Q2 QTD-Current Qtr Avg

Q2 QTD-Prior Qtr-Avg

Q2 QTD-Total Avg

Q3 QTD-Current Qtr Avg

Q3 QTD-Prior Qtr-Avg

Q3 QTD-Total Avg

Q4 QTD-Current Qtr Avg

Q4 QTD-Prior Qtr-Avg

Q4 QTD-Total Avg

Current Qtr

Activity 1

[Null]

Activity 2

[Null]

Activity 3

[Null]

Activity 4

[Null]

Activity 1

[Null]

Activity 2

[Null]

Activity 3

[Null]

Activity 4

[Null]

Activity 5

[Null]

Activity 1

[Null]

Activity 2

[Null]

Activity 3

[Null]

Activity 4

[Null]

Activity 1

[Null]

Activity 2

[Null]

Activity 3

[Null]

Activity 4

[Null]

Activity 5

[Null]

Activity 6

[Null]

RESULTS:
If Current Qtr Field = Q2, only show the 3 fields that contain Q2 in the name

Activity Classification	Q2 QTD-Current Qtr Avg	Q2 QTD-Prior Qtr-Avg	Q2 QTD-Total Avg
Activity 1	22	6	28
Activity 2	7	32	39
Activity 3	8	8	16
Activity 4	88	9	97
Activity 5	10	1	11


If Current Qtr Field = Q4, only show the 3 fields that contain Q4 in the name

Activity Classification	Q4 QTD-Current Qtr Avg	Q4 QTD-Prior Qtr-Avg	Q4 QTD-Total Avg
Activity 1	8	8	16
Activity 2	19	4	23
Activity 3	49	2	51
Activity 4	33	12	45
Activity 5	18	12	30
Activity 6	4	25	29

AYX Question-Dynamic Select-Data & Results.xlsx

Help

Accepted answers

grazitti_sapna

Hi @J-Riedel , try the workflow attached as you provided the current quarter column so I leveraged that for my workflow if you need to calculate the current quarter on the basis of today's date let me know I will update the workflow. It is just one of the ways to solve the problem.

Thanks!

j-riedel_workflow.yxmd

All comments

grazitti_sapna

Thanks!

j-riedel_workflow.yxmd

Matthew

this should do the trick.. and then after the dynamic select tool, just filter out the null rows

though of course i made the assumption that you are interested in the current quarter of the year, not the quarter that is in the "current quarter" column

contains([Name],"Q"+tostring(ceil(DateTimeMonth(DateTimeToday())/3))) or contains([Name],'Activity Classification')

J-Riedel

Thank you Sapna. This works perfectly since it can be based on the Q# that I am already brining into the workflow. I appreciate the quick response and help!

grazitti_sapna

@J-Riedel I am glad that I could help you and thanks for accepting my post as a solution. Much appreciated!

Quick Links

Unanswered
Community Events
Groups

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703