Creating a Calculated Column where the Column Name is a Calculated Date

[Timothy Coluccio]

Hello! I am trying to create a calculated column which has a name that changes based on a custom expression. So for example, I used this case statement to calculate a week ending date as the custom expression: case when DayOfWeek(DateTimeNow())=0 then DateAdd("dd",5,date(DateTimeNow())) when DayOfWeek(DateTimeNow())=2 then DateAdd("dd",3,date(DateTimeNow())) when DayOfWeek(DateTimeNow())=3 then DateAdd("dd",2,date(DateTimeNow())) when DayOfWeek(DateTimeNow())=4 then DateAdd("dd",1,date(DateTimeNow())) when DayOfWeek(DateTimeNow())=5 then date(DateTimeNow()) when DayOfWeek(DateTimeNow())=6 then DateAdd("dd",6,date(DateTimeNow())) else NULL END I would then like the column name to be a concatenate of that plus the words "Week Ending". So for example, if today's date is 1/31/19, the column would calculate the date 2/1/19 and the name of the column would be "Week Ending 2/1/19". Or something like that. Is this possible

[Gaia Paolini]

Hi

If I understand correctly, you want a calculated column, the name of which is equal to its value, give or take a prefix.

You could create a column using an IronPython script. However I have some difficultiesworking out this use case:

1) I don't know if there is a way to assign a column name equal to its (just calculated) value, considering that a column's value in principle, though not in this case, varies from row to row. There might be a way of creating the column with a temporary name, extracting the unique values of this column, then renaming this column with the extracted value.But it is a lot of scripting.

2) Whenyou apply this script again, DateTimeNow() will have moved forward. So the content and the name of the column will have changed. Say every week you create thiscolumn.At the end of the month you end up with a bunch of columns, each of which states a different date, with no reference to the date when you created it. Does it give you any actionableinformation

Maybe if you explain the use case and goal I might be able to help more.

Gaia

[James Watts]

Create a calculated column in your existing table with this 'Week Ending....' text.

Add a new data table, based on the existing one. Insert a pivot transform. Put in the new column as the column name, and use all the other columns as row identifiers.