Hi Community, I have months as field names in the data: * January * February * March...till December I wish to dynamically select columns till current month. For example - since we are in April, I'd like to select - * January * February * March * April If we are in December, we'd select all columns from January through till December. Is there a formula that I can use within dynamic select to select current month and all previous months of the current year? dynamic_months_selection.yxmd

Dynamically select columns - current month and all previous months

Hi Community,

I have months as field names in the data:

January
February
March...till December

I wish to dynamically select columns till current month. For example - since we are in April, I'd like to select -

January
February
March
April

If we are in December, we'd select all columns from January through till December.
Is there a formula that I can use within dynamic select to select current month and all previous months of the current year?

dynamic_months_selection.yxmd

Transformation

Accepted answers

binu_acs

@amadra update the formula in the dynamic select tool

[FieldNumber] <= DateTimeMonth(DateTimeToday())

dynamic_months_selection.yxmd

All comments

caltang

A high level solution would be to have the months as numbers.

For example, January corresponds to 1, February corresponds to 2, and so on until 12. You do not need to change the Monthname to a number per se, just add a new column that indicates as such, and change the date type to Int16 will do.

From there, you can make a condition that if you select December (12), then anything less than or equal to 12 will be selected as well. This is your row-level control.

You can use this row-level control and combine it with Cross-Tab tool to become the columns you want. The added benefit is that your columns will be sorted in order. If you use monthname instead, then your columns will not be arranged accordingly (automatically arranged by Alphabet).

Then, you can use a dynamic rename with formula to change the numbers back into monthnames.

Try it - this will be useful.

caltang

I've done something simple for you to have a look and experiment further. What I would suggest is to include an Interface to make this workflow into an Analytic App. Then you can choose the month of which you conduct your analysis, making it even more dynamic.

For now, I am using:

ToNumber(substring(DateTimeParse(DateTimeFormat(DateTimeToday(),"%b"), "%b"),5,2)) <= [MonthNo]

As anyone can quickly tell, DateTimeToday() is very dependent on your day today. It is dynamic if you're certain of the date you conduct your analysis, but if you wish to change the date to a previous date, then this fails. The analytic app will come in and replace that String with the Date of your liking, or Month of your liking.

Experiment with this and see how it goes.

Happy Alteryx-ing!

dynamic_months_selection.yxzp

Raj

please find the solution attached

dynamic_months_selection.yxmd

binu_acs

@amadra update the formula in the dynamic select tool

[FieldNumber] <= DateTimeMonth(DateTimeToday())

dynamic_months_selection.yxmd

caltang

Awesome how simpler this is, thanks for sharing @binu_acs !

Quick Links

Unanswered
Community Events
Groups

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335