en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Maurice Reid  
#1 Posted : Thursday, July 18, 2013 4:39:00 PM(UTC)
Maurice Reid
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/27/2013(UTC)
Posts: 11

UserPostedImage

Hello All

I have a custom hierarchy with 5 levels set up on a cross table with a date hierarchy on the top axis. What I need to do is create calculations for each date based on specific sub totals from different levels of the custom hierarchy.

From the screenshot uploaded how can I for example get the:
1. Total Cost of Sales on Level 3 when level 4 is expanded?
2. Get the total of Other Income when it is not expanded and still get the value even after it is expanded.

And is it possible to get the subtotal of a sub level in the hierarchy even when it is not expanded/seen?
Also the values in the custom hierarchy are not fixed, it may change based on the company selected.

Any help is appreciated. Thanks.
Páll Bjarkason  
#2 Posted : Friday, July 19, 2013 9:54:22 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Hi Maurice

Don’t know if I understand your problem correct. But maybe there is a simple solution…

Q1: You can get the total Cost of Sales on Level 3 by using then built in grand totals and sub totals functions.
Q2: The totals do not require any manual calculations and work regardless the hierarchy is collapsed or expanded.

UserPostedImage

Kind regards Páll





Best regards

Páll, CTS
Maurice Reid  
#3 Posted : Friday, July 19, 2013 2:37:31 PM(UTC)
Maurice Reid
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/27/2013(UTC)
Posts: 11

Thanks for your response Páll.

Sorry I was not clear enough. What I meant was how do I refer to the totals and subtotals in the various levels of the hierarchy in a calculation formula using dimension referencing, even when the hierarchies are expanded to different levels?

David L. Bojsen  
#4 Posted : Monday, July 22, 2013 2:09:10 PM(UTC)
David L. Bojsen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 3/21/2013(UTC)
Posts: 19

maurice.reid@ansamcal.com wrote:
Thanks for your response Páll.

Sorry I was not clear enough. What I meant was how do I refer to the totals and subtotals in the various levels of the hierarchy in a calculation formula using dimension referencing, even when the hierarchies are expanded to different levels?



Hi Maurice

I would like to help you, but unfurtunally I do not fully understand your question.
Could you please try to explain once more ? Perhaps with a pseudo representation of the calculation you would like, and where you want it.
Venlig hilsen / Best regards

David L. Bojsen
Kapacity A/S

UserPostedImage

Any statement made by me in this forum represents my own opinion and state of mind, and is not necessarily related to or representative of my employer Kapacity A/S.
Maurice Reid  
#5 Posted : Thursday, July 25, 2013 8:48:03 PM(UTC)
Maurice Reid
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/27/2013(UTC)
Posts: 11

Thanks for your interest David.

What I need to do is to create a calculated row based on values at different levels of the hierarchy that I posted in the screenshot. So for example if I need to do the calculation: Total Variances + Other Income. My formula would be sum(0, @"[Cost Of Sales].[Variances]"(r1), 0) + sum(0, @"[Other Income]"(r0), 0).

This would work as long as my hierarchy is expanded all the way down so that I can see the Total Variance on Level 04 as it is in the screenshot. But if I were to roll back up the hierarchy to view just the Cost of Sales (Level 02) at a summary level this would break my calculation.

Is there any way to avoid this?
Páll Bjarkason  
#6 Posted : Monday, July 29, 2013 2:05:39 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Hi Maurice

Note: If it’s not necessary for the user to see all of the hierarchy you could choose to hide parts of it, such that the end user does not collapse it, and accidently wrecks the calculation.

You can then reference parts of the hidden crosstab in order to get the decried calculation correct every time.
Best regards

Páll, CTS
Maurice Reid  
#7 Posted : Tuesday, July 30, 2013 2:18:30 PM(UTC)
Maurice Reid
Rank: Advanced Member

Groups: extranet\Forum
Joined: 5/27/2013(UTC)
Posts: 11

Thanks Páll I guess I'll have to work with your solution.
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error