Calculating percentages in tm1.

[karan2345]

Hi Guys,

I have a Sales cube having the following dimensions

1.Sales Facility
2.Year
3.Month
4.Market Segment
5.Product line
6.Market
7.Item Sales Group
8.Scenario
9.Sales Measures(Amount,Amount %,Amount PY,Amount % PY,Pieces,Pieces %,Pieces PY,Avg Price PY,Avg Price)


I want the percentage of the Total Amount to be displayed as the user navigates through the entire cube.

['Amount %','Actual']=['Amount','Actual']\['Total Months','Total Market Segment','Total Product Line','Total Market','Total Item Sales Group','Actual','Amount']*100;

I am using this formulae for calculating the Amount % but it works only when I have all these dimension selected at the consolidation level, if i select leaf level elements it is not showing the % as expected.

For Eg;
I have attached a file.
In this ideally when the user looks at the cube the % should be based on the elements selected and the value for the total months.

But because of the formula it uses the value of
'Total Months','Total Market Segment',
'Total Product Line',
'Total Market',
'Total Item Sales Group'


How can i make this work, I request some one to please help me on this.

Thanks.

[Duncan P]

In this ideally when the user looks at the cube the % should be based on the elements selected and the value for the total months.
Thanks.

If this is the case why not just divide by the figure for total months, leaving the elements on the other hierarchical dimensions unspecified?

Code: Select all

['Amount %','Actual']=['Amount']\['Total Months','Amount']*100;

[qml]

To me it's not really clear what you want to achieve and so I don't know if your rule is doing that. Having said that - the rule itself looks like it should calculate something.
Are you sure there are no results showing up on leaves? Maybe they are so small that with the precision you're using they are rounded down to 0.00? You should right click on any of the cells where you expect a value and use the "Trace Calculation..." tool (as always when debugging rules).

[karan2345]

To me it's not really clear what you want to achieve and so I don't know if your rule is doing that. Having said that - the rule itself looks like it should calculate something.
Are you sure there are no results showing up on leaves? Maybe they are so small that with the precision you're using they are rounded down to 0.00? You should right click on any of the cells where you expect a value and use the "Trace Calculation..." tool (as always when debugging rules).

Hi qml,

Thanks for the help.

Yes it calculates the value but that is not what i expect it to do.

As you can see in my calculation I am taking total of all dimension and using that value to get the %, this would be good when the user is viewing the data at that level,
but as soon as the user selects a different view the % is not coming as expected.

Is there a way to find out what is the level of cell selected by the user and do calculation based on that?

For instance the user might select Months in the column as given in the attached file.

So for this I will use 'Total Months' as my basis to calculate %.

if the user views it by Market Segment then

I will use 'Total market segment' as the basis to calculate %.
and so on for all the other dimensions.

Can this be done by using some logic in tm1?

[qml]

Can this be done by using some logic in tm1?

Not the way you want it, it can't be fully contextual like that. However you can create separate measures to capture percentages with respect to total year, total market etc.

[karan2345]

Not the way you want it, it can't be fully contextual like that. However you can create separate measures to capture percentages with respect to total year, total market etc.

Thanks for resolving the doubt in my mind.Appreciate that.

[tomok]

As anyone who works with TM1 will often find, there are going to be situations where you "could" calculate something in a cube but it would be best not to. This is one of those cases. You can do what you are asking quite easily with an Active Form report. It won't be in a cube view but still would be accessible through the Excel template or TM1Web. Oh and FYI, DO NOT ask me how to do this in Excel. If you can't figure out how to write a formula in Excel to do this then you have no business masquerading as a developer.

[karan2345]

Oh and FYI, DO NOT ask me how to do this in Excel. If you can't figure out how to write a formula in Excel to do this then you have no business masquerading as a developer.

Ha ha , Surely I will ask you this question JK

Thanks for the help. I know I ask very intermediate level questions in this forum, extremely sorry for that, but I have no one else to seek help from when I get stuck, For my company I am the project manager, the functional guy, a beginner or whatever you want to say , my team comprises of me, me and me and I am on the firing zone as well.

Anyways thanks to you guys, I am alive. Thanks for the help.

[tomok]

Thanks for the help. I know I ask very intermediate level questions in this forum, extremely sorry for that, but I have no one else to seek help from when I get stuck, For my company I am the project manager, the functional guy, a beginner or whatever you want to say , my team comprises of me, me and me and I am on the firing zone as well.

Corporate stupidity never ceases to amaze me. How they can spend hundreds of thousands of dollars on a piece of software and not be willing to spend just a tiny bit more and actually get someone who knows what they are doing (no disrepect intended) to implement it is beyond my comprehension. This is NOT a learn as you go tool. It used to be, many years ago when it was still Applix and pretty cheap, but the software investment is too great now to just stumble your way through an implementation.

[Alan Kirk]

Thanks for the help. I know I ask very intermediate level questions in this forum, extremely sorry for that, but I have no one else to seek help from when I get stuck, For my company I am the project manager, the functional guy, a beginner or whatever you want to say , my team comprises of me, me and me and I am on the firing zone as well.

Corporate stupidity never ceases to amaze me. How they can spend hundreds of thousands of dollars on a piece of software and not be willing to spend just a tiny bit more and actually get someone who knows what they are doing (no disrepect intended) to implement it is beyond my comprehension. This is NOT a learn as you go tool. It used to be, many years ago when it was still Applix and pretty cheap, but the software investment is too great now to just stumble your way through an implementation.

I'm sure that Karan is capable of learning the tool. However these days investing in one of the IBM training courses (or courses run by some consulting firms) is pretty much essential if a user hasn't had experience with the tool previously.

[karan2345]

I'm sure that Karan is capable of learning the tool. However these days investing in one of the IBM training courses (or courses run by some consulting firms) is pretty much essential if a user hasn't had experience with the tool previously.

I have gone through this course, but I suppose u need project experience before you are good to go and do it all on your own, and for a beginner like me I feel there should be a senior guy along with me who has good amount of experience to guide me.

[karan2345]

Corporate stupidity never ceases to amaze me. How they can spend hundreds of thousands of dollars on a piece of software and not be willing to spend just a tiny bit more and actually get someone who knows what they are doing (no disrepect intended) to implement it is beyond my comprehension. This is NOT a learn as you go tool. It used to be, many years ago when it was still Applix and pretty cheap, but the software investment is too great now to just stumble your way through an implementation.

Hi Tom,

I think the customer is not at any fault because he is paying for an expert consultant and unfortunately my organisation has projected me as the senior most experienced person in Cognos who has tons of experience (just 3 months) for "Cognos Planning" in my organisation and I feel its the biggest joke for me when my organisation thinks like that. Well I cant do much in this, I am helpless.
Its a chicken and leg situation for everyone, I cant deny to work on the project because I also want project exposure.

The end result is everyone is at loss.

[Alan Kirk]

I'm sure that Karan is capable of learning the tool. However these days investing in one of the IBM training courses (or courses run by some consulting firms) is pretty much essential if a user hasn't had experience with the tool previously.

I have gone through this course, but I suppose u need project experience before you are good to go and do it all on your own, and for a beginner like me I feel there should be a senior guy along with me who has good amount of experience to guide me.

You can always (and possibly should) hire one. Depending on where you are there are plenty of good consulting firms out there who can do the initial implementation for you (as long as you give them some clear parameters) and guide you along the way to taking ownership of the model yourself.

[karan2345]

You can always (and possibly should) hire one. Depending on where you are there are plenty of good consulting firms out there who can do the initial implementation for you (as long as you give them some clear parameters) and guide you along the way to taking ownership of the model yourself.

Yes, I have already given this suggestion to my organisation and probably in the next project they "might" hire some one an external consultant or a perm employee who is an expert in TM1 and Cognos BI Reporting.

But that may be in the next project, as of now I will have to face the music.

[tomok]

You can always (and possibly should) hire one. Depending on where you are there are plenty of good consulting firms out there who can do the initial implementation for you (as long as you give them some clear parameters) and guide you along the way to taking ownership of the model yourself.

I think he is saying that he is the consultant. His company has sold him to the customer as an experienced consultant and now he is having to struggle through the implementation. I feel his pain, but at the same time am disgusted with these outfits that "buy" work at ridiculously low rates (not saying that is the case here but I'll be willing to bet it is) and then make their people suffer until they get experience. Not only does it drive down consulting rates, it gives us all a bad name. [/rant]

[Alan Kirk]

You can always (and possibly should) hire one. Depending on where you are there are plenty of good consulting firms out there who can do the initial implementation for you (as long as you give them some clear parameters) and guide you along the way to taking ownership of the model yourself.

I think he is saying that he is the consultant. His company has sold him to the customer as an experienced consultant and now he is having to struggle through the implementation. I feel his pain, but at the same time am disgusted with these outfits that "buy" work at ridiculously low rates (not saying that is the case here but I'll be willing to bet it is) and then make their people suffer until they get experience. Not only does it drive down consulting rates, it gives us all a bad name. [/rant]

Yes, my earlier post crossed with the one where he mentioned that. Prior to that I thought that he was an employee rather than a consultant. I agree with you on that point; nobody should be out in the market pretending to employ skilled people who in fact aren't.

[karan2345]

am disgusted with these outfits that "buy" work at ridiculously low rates (not saying that is the case here but I'll be willing to bet it is)
[/rant]

I don't know at what rates have they bought it, but because of the false impression & unrealistic deadlines given by them to the customer, I am the one who is suffering & the customer of course.

Anyways no point in debating it, I have to live with it.

All I can say is thanks to you guys and extremely sorry for the silly questions that I might have posted in this forum.

[lotsaram]

I think the customer is not at any fault because he is paying for an expert consultant and unfortunately my organisation has projected me as the senior most experienced person in Cognos who has tons of experience (just 3 months) for "Cognos Planning" in my organisation and I feel its the biggest joke for me when my organisation thinks like that. Well I cant do much in this, I am helpless.
Its a chicken and leg situation for everyone, I cant deny to work on the project because I also want project exposure.

The end result is everyone is at loss.

Yes everyone loses but the customer is not blameless. A certification is just a piece of paper and no substitute for experience and experience counts when it comes to implementing an OLAP based planning system, much more than a relational based reporting system, and doubly so when there are tight deadlines. If the customer believes that all consultants are equal and all personnel interchangeable and it is just a matter of the lowest rates then all the more fool them, especially if they don't have a competent enough project manager on the case to realise that the supposedly expert consultant is floundering and doesn't know what they are doing.

[warrenl]

Code: Select all

['Sales Measures’:’Amount % PY’] = DB('Sales Budgeting',!Sales Facility,!Year,!Month,!Market Segment!,!Product line,!Market,!Item Sales Group,!Actual Budgeted,’Amount PY’)/ DB('Sales Budgeting',!Sales Facility,!Year,’Total Months’,!Market Segment!,!Product line,!Market,!Item Sales Group,!Actual Budgeted,’Amount PY’)

Like you I'm not a consultant but we did have the fortune of getting a consultant in to create the first cubes and rules. I looked at some examples of percentag cals and with those in mind I recently was playing around with Cognos Insight and found the rule writer very easy to use. Entering an apostrophe brings up a list of expected entries which takes the worry away about correct syntax.

Hope the above coding will help otherwise, being my first post in this forum, it was a good exercise for me.

Cheers