String in consolidation level

[2limit2]

Hi,

I have a cube that have 4 dimensions: Year, Month, Jobs, Jobsinfo. Jobsinfo has a start date (string). How do I make this string date appear at consolidated level? When I change the month to Q1, all the jobs are listed, but with a blank start date.

Any ideas?

[declanr]

Hi,

I have a cube that have 4 dimensions: Year, Month, Jobs, Jobsinfo. Jobsinfo has a start date (string). How do I make this string date appear at consolidated level? When I change the month to Q1, all the jobs are listed, but with a blank start date.

Any ideas?

In order to show a C-level string element have some resemblance to the n-level strings below it you will need to write specific rule telling TM1 what it should see.

For example:

Element String Measure
Jan Turnip
Feb Ferrari
Mar Spain

TM1 has no way of knowing what Quarter-1 should show based on the elements below it since Turnip + Ferrari + Spain = ???

You can of course write a rule to have any c-level elements showing the last n-level child's string value, so in the case above Quarter-1 would say Spain but is that the sort of rule that should be implied in your case?

[2limit2]

Hi,

I have a cube that have 4 dimensions: Year, Month, Jobs, Jobsinfo. Jobsinfo has a start date (string). How do I make this string date appear at consolidated level? When I change the month to Q1, all the jobs are listed, but with a blank start date.

Any ideas?

In order to show a C-level string element have some resemblance to the n-level strings below it you will need to write specific rule telling TM1 what it should see.

For example:

Element String Measure
Jan Turnip
Feb Ferrari
Mar Spain

TM1 has no way of knowing what Quarter-1 should show based on the elements below it since Turnip + Ferrari + Spain = ???

You can of course write a rule to have any c-level elements showing the last n-level child's string value, so in the case above Quarter-1 would say Spain but is that the sort of rule that should be implied in your case?

Yes, as in my case the start date of each job would be the same at any quarter.

For example:

Jan start date
Job 1 1-Jan-12

Feb
Job 1 1-Feb-12

Q1
Job 1 1-Jan-12
Job 1 1-Feb-12

[Harvey]

You need something like:

Code: Select all

['Start Date'] = 
    IF(
        ELLEV('Month', !Month) <> 0),
        DB('YourCube', ... , ELCOMP('Month', !Month, 1), ...),
        CONTINUE
    );

I haven't tested this, and just wrote it off the top of my head, but it should give you the general idea of the approach.

However, date strings are not the only way to go, and you might want to consider using a numerical value to represent a date serial. Have you looked at this option? In most cases, it's the most efficient way to deal with dates in TM1.

[Andy Key]

TM1 has no way of knowing what Quarter-1 should show based on the elements below it since Turnip + Ferrari + Spain = ???

Err, Turnip + Ferrari + Spain? Surely Quarter-1 should show Fernando Alonso...

[2limit2]

You need something like:

Code: Select all

['Start Date'] = 
    IF(
        ELLEV('Month', !Month) <> 0),
        DB('YourCube', ... , ELCOMP('Month', !Month, 1), ...),
        CONTINUE
    );

I haven't tested this, and just wrote it off the top of my head, but it should give you the general idea of the approach.

However, date strings are not the only way to go, and you might want to consider using a numerical value to represent a date serial. Have you looked at this option? In most cases, it's the most efficient way to deal with dates in TM1.

What does ELLEV and ELCOMP do? Newbie here.

I sort of just "inherited" TM1 from someone who used to work for the company. The dates were setup as strings. The start dates will not be used for any calculation purposes which is probably why they were set up this way. I will look into this later on but will have to work with what I have for now, as I am still learning this. Thanks for your insight.

[Alan Kirk]

What does ELLEV and ELCOMP do? Newbie here.

From Request for assistance guidelines (PLEASE READ)

6) Give a brief outline of what you've done to find the answer; which manuals or guides you've looked in,

From FAQ Links

Frequently Referenced Sections:
The Reference Guide. Arguably the single most important document in the library. It contains descriptions of most Rules, Worksheet and TI functions, as well as a guide to Rule syntax .

[2limit2]

Alan Kirk, thank you for pointing me to the reference guide

I looked at ELLEV and ELCOMP but I don't think this applies to my problem as I am looking at the this from a cube viewer.

Allow me to be more specific this time.

Cube New Sales, contains 3 dimensions, Jobs, Sales, Month.
Jobs dimension contain jobs elements. At leaf level are Job Numbers, which consolidates to Job Area, which then consolidates to Job City.
Sales dimension contain sales info elements, ie. Job Count, Status (string), Start Date (string), Selling Price, Cost of sale, Margin, % Margin, etc.
Month dimension contain month elements, Jan... Dec, which consolidates to Jan YTD, Feb YTD, Mar YTD, which then consolidates to Total Year.

When I click the Month dimension and choose Jan, I see the status and start date of the jobs that sold in January.
When I click the Month dimension and choose Jan YTD, the status and start date disappears. The others that are numeric type are working properly.

How do I make the status and start date carry over to the consolidated level?

[tomok]

How do I make the status and start date carry over to the consolidated level?

Lazarus gave you the answer. Is there a reason you've chosen to ignore it?

[Alan Kirk]

Alan Kirk, thank you for pointing me to the reference guide

I looked at ELLEV and ELCOMP but I don't think this applies to my problem as I am looking at the this from a cube viewer.

{Snip}

How do I make the status and start date carry over to the consolidated level?

Where you're looking at it from is not material.

Do you understand the concept of using Rules to define values within a cube? If not you need to have a read through the Rules Guide. This will not be an easy or quick journey, and the concept of Feeders even less so, but it's one that you will have to undertake if you want to administer a TM1 system.

The short answer is that strings, by their very nature, do not consolidate. Numbers do, strings don't. In the majority of cases "adding" strings together would simply give nonsense values as the example in Declan's post showed.

To do the sort of thing that you're describing, you therefore need to use a rule formula to define exactly what you want the string in the consolidated cell to be; in this case drawing it from the underlying level 0 elements.

This is what Lazarus was describing.

[2limit2]

How do I make the status and start date carry over to the consolidated level?

Lazarus gave you the answer. Is there a reason you've chosen to ignore it?

Actually, I did try Lazarus' answer but it didn't work for me. I thought this was because the reference guide stated this to be a worksheet function only. I will try it again.

Alan Kirk, I do understand basic concepts of rules and have rules in this particular cube. And I understand why strings wouldn't consolidate. I'm looking for a workaround on this constraint though.

[whitej_d]

If you're trying to do this soley in the cube viewer, then the answer is you can't.

You'd have to get admin rights and do some development to program in the functionality you need, as suggested above.

Your other option would be to load the job start dates in as attributes to the job, seeing as they are a 1 to 1 relationship. Then you add a rule to the cube:

['Job Start Date'] = S: ATTRS('Jobs', !Jobs, 'Job Start Date');

[Alan Kirk]

How do I make the status and start date carry over to the consolidated level?

Lazarus gave you the answer. Is there a reason you've chosen to ignore it?

Actually, I did try Lazarus' answer but it didn't work for me. I thought this was because the reference guide stated this to be a worksheet function only. I will try it again.

If you take a look in the reference guide that I pointed you to previously you'll see that it consists of groupings of functions; Rules functions in one section, Worksheet functions in another, TI functions in another and so on. Some of the Rules functions can be used in TI, and some of the worksheet functions will have the same name as Rules functions. So the help topic that you looked at was probably quite correct; you were looking at the Worksheet function topic, but need to be looking at the corresponding Rules function topic. (In reality they'll usually have the same arguments and return the same values, but it's just a better practice to look at the one that corresponds to the environment that you're using it in.)

Mind you, it would be possible to construct some nested worksheet functions to pull the value down in a predefined report the way Lazarus has suggested but (a) that would be a high maintenance and therefore ugly solution, and (b) it won't be of any use when you're browsing in cube viewer. Better to do it on the server side, and that means using Rules.

Alan Kirk, I do understand basic concepts of rules and have rules in this particular cube. And I understand why strings wouldn't consolidate. I'm looking for a workaround on this constraint though.

The workaround is... to use rules to create the entry at consolidation level. Lazurus' approach is one option, whitej_d's is another.

[2limit2]

If you're trying to do this soley in the cube viewer, then the answer is you can't.

You'd have to get admin rights and do some development to program in the functionality you need, as suggested above.

Your other option would be to load the job start dates in as attributes to the job, seeing as they are a 1 to 1 relationship. Then you add a rule to the cube:

['Job Start Date'] = S: ATTRS('Jobs', !Jobs, 'Job Start Date');

I tried this. Load the start dates as attributes to the job and then added the rule the the cube. I got the error, "Rule applies to cell".

Any thoughts?

[tomok]

Any thoughts?

My thought would be that if you are going to ask busy people to give you free help then you need to take the time to give us all the information we need to assist you without having to work to get it. You tried it??? What exactly did you try? Where is your rule code? How do you know you did it right? You get a "rule applies to cell" error message. Exactly where do you get this message?

[Alan Kirk]

If you're trying to do this soley in the cube viewer, then the answer is you can't.

You'd have to get admin rights and do some development to program in the functionality you need, as suggested above.

Your other option would be to load the job start dates in as attributes to the job, seeing as they are a 1 to 1 relationship. Then you add a rule to the cube:

['Job Start Date'] = S: ATTRS('Jobs', !Jobs, 'Job Start Date');

I tried this. Load the start dates as attributes to the job and then added the rule the the cube. I got the error, "Rule applies to cell".

Any thoughts?

I sense a certain frustration in Tomok's reply, which I can understand (in fact my thought was along the lines of at least the last two sentences of his reply), though I also understand that you're still trying to get your head around some of this stuff. But could I again point you to the Request For Assistance Guidelines, and recommend that you read through them again? If you get an error then no-one can give you an answer unless they know WHERE the error is, and WHAT you're doing when you get it. And as both Tomok and the guidelines state, the actual code that you're using is far more useful than anything else in diagnosing a problem.

[rmackenzie]

I got the error, "Rule applies to cell".

Did the error refer to the point at which you tried to load the attributes, or at the point at which you tried to save the cube rule with the new rule for 'Job Start Date' ? I ask because the error message 'Rule applies to cell' is one given in a TI error message log file. So let's consider the following:

1. You might have changed a TI somewhere and got that error message? Is there a problem posting this bit of code that should create/ update the 'Job Start Date' attribute on the 'Jobs' dimension?
2. It sounds like you changed the cube rule to include the line suggested by whitej_d. Presumably it compiled as you are not reporting an error - so what is the value in the 'Job Start Date' measure when you inspect your cube view?

Any thoughts?

Maybe that you might be out of your depth and you should ask around for some training instead of seeking help through online forums. Also, you might consider that it's rarely possible to ask questions on internet, get an answer, copy and paste a bit of code into your system, and get the desired result...

[whitej_d]

I believe you haven't removed the original code which loads the Job start date into the original cube.

Seeing as you are now loading into the attributes cube and referencing to the reporting cube, you no longer need to include the Job Start Date in the data load to the original cube.

[2limit2]

If you're trying to do this soley in the cube viewer, then the answer is you can't.

You'd have to get admin rights and do some development to program in the functionality you need, as suggested above.

Your other option would be to load the job start dates in as attributes to the job, seeing as they are a 1 to 1 relationship. Then you add a rule to the cube:

['Job Start Date'] = S: ATTRS('Jobs', !Jobs, 'Job Start Date');

I followed whitej_d's suggestion and this has worked for me.

I added a new attribute to the New Jobs dimension called Firm Date, then used a rule to display this attribute in the cube viewer. Then used an if statement so that it will only display if a job is a new sale for the month. At a consol level, ie. Nov YTD, the Firm Date value is showing up, which is exactly where I needed to be.

Here is the rule I used:
['Firm Date']=S:if(['Job Count']<>0,ATTRS('New Jobs 2', !New Jobs 2, 'Firm Date'),continue);

FEEDERS;
['Job Count']=>['Firm Date'];

It took a while. I needed to wrap my head around it, but I'm learning a lot as I go along. Thank you very much for your help.