How to use Time Period Format in a Date Formula
The date formula requires you to use numbers for the years, months, and days. I have 2 line items in a module that have the year, and a custom month list that contains the members of Jan, feb, mar, etc... For the month, I have a property on that list with the month number, so I can easily pull that into the date formula, but year cannot be pulled in. Trying to use year directly gets: "The 'year' argument for the 'DATE' function should have datatype of NUMBER not TIME PERIOD: DATE(year:NUMBER, month:NUMBER, day:NUMBER)" I can't convert it to text or a value or anything, so there is I don't see a way to populate the current period using a line item with a time period format. Has anyone run across this yet?
Sorry for the late reply.
You can use the formula YEAR() to convert a Year-formatted line item to a number, and use it in a DATE()-formula. For example DATE(YEAR("year formatted line item"), 1, 1) gives 20xx-01-01
But I agree that you shouldn't have to convert it. It would be easier if DATE() took both numbers and periods. You should write a request for enhancement about it.1
do you use value function which converts text to number?0
It isn't "text", it is essentially a list item. I haven't tried, but I wonder if Value(name()) would work since the name would return the text version of the year, then value could convert it to a number.
Either way, it seems like a lot of hoops to jump through to use a year formatted list item as a year in a date formula.0
Why aren't you using just one line item with year and month time period? I agree that it is a hassle to do what you want to do. But Im not sure I understand why you would want to write/select year and then write/select month to create a date. Time period would do all that for you.
If you could give some more information about what you are doing with the date formula, maybe we could help out.
It is mostly with data integration, and areas that I use custom date dimensions to get graphs to look halfway decent.
With Data integration, month and year are separate dimensions coming in from other source systems. I have them mapped into those custom date dimensions for graphing purposes, but then I have to do all of these conversions just to get it into a date format.
It is just something that doesn't make sense to me. Why would you add list items for year and month, and not allow them to be used in a date formula?0