Checking for a valid date

[tm1_user]

I have a cube which has 'Days' dimension and 'Month' dimension and some other dimensions too.
The Days dimension has elements Day 1 to Day 31. 'Month' dimension has all the 12 month names January to December.

In the cube view, While selecting the Month (say June) under 'Month' dimension, I want to restrict the number of elements of Days based on number of days in that Month i.e 30.

Is there any way this can be achieved?

I am trying to do is to generate INSERT statements and inserting the data to SQL Server using ODBCOutput. It is failing with invalid Date (eg 2015-06-31 which is out of range)
Can we ignore such INSERT statements in the ODBCOutput?


Cheers

[tomok]

My question to you is why do you have data in your cube for an invalid date? How did it get there? Wouldn't it be a better idea to control the input to only valid dates instead of controlling the output?

[declanr]

You can just have 1 numeric measure formatted as a date which will only allow valid date entries in your specified format.

Or if you MUST have it set via 3 distinct string measures of "Year", "Month" and "Day" then you can use an attribute picklist for Year and Month with a Picklist Cube to bring back the picklist for day - I would base this on a subset of a day dimension with subsets of 28, 29, 30 and 31; most months would be straight forward (so if they have selected "Jan" then bring back the "31" subset as your picklist) however you would need a specific rule for February that recognises whether or not it is a leap year. I wrote a TI process in a VERY similar post that you probably found when you searched the forum that shows how to do that in TI (it's simple logic that is obviously not specific to TM1) you would just need to build it into a rule statement instead of a TI statement (again pretty simple) but all in all the date formatted numeric measure is the simplest solution. http://www.tm1forum.com/viewtopic.php?f ... ilit=+date

[tm1_user]

@tomok, yours is a valid question. I wanted to handle the case when user enters data in a cell beyond end date of the month. Though, the data is entered accidentally, i am checking for options if it can be handled at database insert in the TI process using ODBCOutput.

[declanr]

@tomok, yours is a valid question. I wanted to handle the case when user enters data in a cell beyond end date of the month. Though, the data is entered accidentally, i am checking for options if it can be handled at database insert in the TI process using ODBCOutput.

You use an IF statement if that's all you want to do.

[tm1_user]

You can just have 1 numeric measure formatted as a date which will only allow valid date entries in your specified format.

Or if you MUST have it set via 3 distinct string measures of "Year", "Month" and "Day" then you can use an attribute picklist for Year and Month with a Picklist Cube to bring back the picklist for day - I would base this on a subset of a day dimension with subsets of 28, 29, 30 and 31; most months would be straight forward (so if they have selected "Jan" then bring back the "31" subset as your picklist) however you would need a specific rule for February that recognises whether or not it is a leap year. I wrote a TI process in a VERY similar post that you probably found when you searched the forum that shows how to do that in TI (it's simple logic that is obviously not specific to TM1) you would just need to build it into a rule statement instead of a TI statement (again pretty simple) but all in all the date formatted numeric measure is the simplest solution. http://www.tm1forum.com/viewtopic.php?f ... ilit=+date

Thanks Declan. This approach seems to fit into my requirement especially the leap year calculation!!

Coming to the picklist approach, four subsets (28, 29,30,31) are created on Days dim. In the control cube for Month dim, these subsets are assigned to the corresponding months based on the no. of days in the form of dimension picklist.

After writing a rule on Feb cell, how is the subset assigned? Did I miss anything here in the process? Please correct me.

[declanr]

After writing a rule on Feb cell, how is the subset assigned? Did I miss anything here in the process? Please correct me.

You create a picklist cube that corresponds to your cube in question and then create a rule on that picklist cube; the rule needs to look at the input cubes selection of month (and year for Feb) within an IF statement and populate with string in the picklist format (SUBSET:<dimname>:<subsetname>) dependent on what it brings back.

[tomok]

A TM1 picklist is for controlling data input into a cell in a cube, not for selecting an element in a dimension. If this were an Excel sheet and you wanted to have a drop-down for selecting only valid day numbers, depending on which month was chosen, then you could use Data Validation and do some combination of IF statements and/or VLOOKUPS to get the data validation working. However, this is not going to work in a cube view. If it were me, I would just hard code a "0" into the cells that represent a combination of month and days that are not valid via a rule. That way no one would be able to enter anything in those cells.

[Duncan P]

or an alternative picklist cube rule might be ...

Code: Select all

[] = S:SUBST( 'static: 1: 2: 3: 4: 5: 6: 7: 8: 9:10:11:12:13:14:15:16:17:18:19:20:21:22:23:24:25:26:27:28:29:30:31', 1,  6 + 3 * (some expression returning the number of days) );

[declanr]

Ah yes I read it completely wrong; didn't realise we were talking dimension intersections here. That is a rather strange set up for an input cube.

[tm1_user]

Thank you all for your responses.

I have checked the date validity with the help of Leap Year calculation by Declan.


Cheers