Jump to content
We've recently updated our Privacy Statement, available here. ×

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


Timothy Coluccio

Recommended Posts

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
Link to comment
Share on other sites

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

Link to comment
Share on other sites

  • 3 weeks later...

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...