TM1 Forum

I'm building a forecast model that stores and projects cash flow forecasts up to 13 weeks in the future. Each week, a new rolling forecast is created.

The idea is that the user selects the week they want to view, and the thirteen weeks following that date are derived from the week selected within the sheet.

It all works perfectly within the cubes, and within Xcelerator Client. My problems start when I load the sheet into TM1 Web.

The Dimension elements are in the format "yyyy-mm-dd"

The forecast dates were calculated in Excel/Xcelerator using '=TEXT(D7+7,"yyyy-mm-dd")'

What's odd is that within TM1 web, it seems to be calculating the correct date, but then returning the wrong value for the month.

So 2016-11-18 should have a 'next week' value of 2016-11-25 but returns 2016-00-25 and for the 13 weeks the date is continually correct, but the month is "00".

Am I missing something obvious? I can see a potential fix by breaking each date down into its component parts and concatenating it back together again, but it seems a bit clunky to have to do that.

TM1 Web is very sensitive to the regional settings of the server and many of the date functions are only implemented to support US settings so it is fragile. I can remember a project where we needed to derive a date string as YYYY-MM-DD format and also found that TEXT(value,"yyyy-mm-dd") wasn't reliable.
Solution was to break it down to YEAR(value)&"-"&MONTH(value)&"-"&DAY(value)
Which I think is basically what you are suggesting.

That's effectively what I've had to do.