en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Peter Bagger  
#1 Posted : Tuesday, July 7, 2015 1:41:33 PM(UTC)
Peter Bagger
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/7/2015(UTC)
Posts: 12

Hi,

I have a cross table with 2 dimensions on the x-axis (Country and Company) and 1 dimension (month) on the y-axis. I would like to get the sum of a given calculation from all companies in Denmark --> Specific country, All companies.

Normally I would remove the dimension company, but I need to refer to a calculation that used 2 measures and requires the dimension.


In the end my result should be a graph showing the procentage of how much money is spent outside Denmark compared to total expences per month.

This Calculations works, but how do I change it from company X to all companies?
sum(@@"[Kreditor].[CountryID].&[dk]";@"[Kreditor].[Name].&[Company X]", 0, m3)


In a work around I have succeeded in getting the right procentage as a total per month by removing the company name to the y-axis. The problem with that workaround is that I cannot make a graph based on a total.

best regards
Peter
Páll Bjarkason  
#2 Posted : Thursday, July 9, 2015 12:57:53 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 1/13/2012(UTC)
Posts: 154

Hi Peter

If you have the country dimension as the first dimension on the primary axis this calc. should work.
The level (l) parameter is nice when you want to reference a high/general level of nested dimensions.

sum(0, @"[DK]"(l(1,0)), m1) % sum(0, all, m1)

Use custom calculation -> as a measure, and you get the DK % each month.

Give it at try and let me know.

Check this out http://www.targit.com/en/resources/forum?g=posts&t=371#post987

Edited by user Thursday, July 9, 2015 1:00:06 PM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Peter Bagger  
#3 Posted : Thursday, July 9, 2015 2:37:48 PM(UTC)
Peter Bagger
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/7/2015(UTC)
Posts: 12

Hi Páll,

thanks for your reply. The solution in the link works fine but is not flexible enough

The calculation look like this:
sum(@"[dk]";"[Company A]":@"[dk]";"[Company Z]", 0, m3)

The problem is that first and last company changes.

I did not manage to get this calculation to work:
sum(@"[DK]"(l(1,0)), 0, m3)

best regards
Peter

Peter Bagger  
#4 Posted : Friday, July 10, 2015 2:08:23 PM(UTC)
Peter Bagger
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/7/2015(UTC)
Posts: 12

Hi again,

I have now changes to 2 dimensions on the x-axis to a hierarchal dimension, and now the sum with levels works:-)

But unfortunately I still have a problem with my calculations.

for each company I have 2 measures and a calculation like below:

X -axis Level 1: Country
Level 2: Company

m1 m2 m3
1 * 1 = 1
1 * 1 = 1
1 * 2 = 2

I then have a column that should show me the sum of m3 in DK:
sum(@"[dk]"(l(1,0)), 0, m3)

What I would like is the sum of m3 which should be 4 (1+1+2). The problem is that I get 12 (3*4).

Any ideas on how to fix that?

best regards
Peter

Edited by user Friday, July 10, 2015 2:10:43 PM(UTC)  | Reason: Not specified

Páll Bjarkason  
#5 Posted : Saturday, July 11, 2015 1:24:02 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 1/13/2012(UTC)
Posts: 154

Could you plz post a screenshot.
I'm in doubt if you want sum or product and how the problem is dimensioned...
Best regards

Páll, CTS
Peter Bagger  
#6 Posted : Monday, July 13, 2015 8:53:38 AM(UTC)
Peter Bagger
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/7/2015(UTC)
Posts: 12

Hi Páll,

see the picture below.
Thanks for all your support.

best regards
Peter

UserPostedImage

Edited by user Tuesday, July 14, 2015 8:16:50 AM(UTC)  | Reason: problem with picture

Páll Bjarkason  
#7 Posted : Monday, July 13, 2015 6:31:00 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 1/13/2012(UTC)
Posts: 154

I can't figure out how Juli differs from the other months...

However if you want some special calculation on the most bottom month try something like:

if xxx then yyy else zzz

The general syntax for all m3 per row is: sum(all,0,m3)

Perhaps you can get some inspiration here:
Top 20 problem
Conditional Calculations Based on Dimension Value not Measure Values
PS The last month can be referenced like this: sum(d1,d-1,m1) …. D-1 is the most bottom row.
PSS We are still in Juli, is this why this month’s value is wrong :)
Best regards

Páll, CTS
Peter Bagger  
#8 Posted : Tuesday, July 14, 2015 8:13:42 AM(UTC)
Peter Bagger
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/7/2015(UTC)
Posts: 12

Hi Páll,

thanks again for all your support. The problem I am facing is a general problem for all months.
I have tried to look at your comments and the other post, but without any luck.


I have made a better screen shot that hopefully explains the problem better

best regards
Peter
UserPostedImage
Páll Bjarkason  
#9 Posted : Tuesday, July 14, 2015 6:19:02 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 1/13/2012(UTC)
Posts: 154

OK. This explains your problem.

The calculation you are looking for in the total column is sum(all,0,m3)
You may have to build a new custom total to achieve this.

The core of the problem pertains to the m2 parameter, which is summed in the total column before the multiplication with the sum of m1.
In order to get a m3 value of 50.571 you need a m2 total off ca. 0,64444 (78.472*0,6444=50.571). I.e. not 1,3.

Hope it helps.
Best regards

Páll, CTS
Peter Bagger  
#10 Posted : Wednesday, July 15, 2015 11:44:19 AM(UTC)
Peter Bagger
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/7/2015(UTC)
Posts: 12

Hi Páll,

true the sum(all,0,m3) gives me the real number, but for all countries. I am only interested in the sum of all m3 from DK!

UserPostedImage

You are also right about m2 total being wrong. But since m1 has to be weighted with m2 it is just as difficult to get a right m2 total as what I have been trying to achieve all along. If it was a constant it would be no problem at all.

You mentioned something about a custom total calculation. Is it possible to manipulate the totals and subtotals?

best regards
Peter
Steffen Davidsen  
#11 Posted : Friday, July 24, 2015 7:22:16 AM(UTC)
Steffen Davidsen
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/8/2014(UTC)
Posts: 28

Originally Posted by: Peter Bagger Go to Quoted Post
Hi Páll,

true the sum(all,0,m3) gives me the real number, but for all countries. I am only interested in the sum of all m3 from DK!

UserPostedImage

You are also right about m2 total being wrong. But since m1 has to be weighted with m2 it is just as difficult to get a right m2 total as what I have been trying to achieve all along. If it was a constant it would be no problem at all.

You mentioned something about a custom total calculation. Is it possible to manipulate the totals and subtotals?

best regards
Peter


Hi Peter!

I think what you are looking for is the "Multidimensional subtotal columns" function, which can be found by right-clicking on your table and choose Table -> Totals and Subtotals -> Multidimensional subtotal columns. (remember to show totals in your tabel after adding it)

This should yield back the totals on the result level of each dimension on the y axis of yours.
Peter Bagger  
#12 Posted : Thursday, August 6, 2015 11:37:19 AM(UTC)
Peter Bagger
Rank: Advanced Member

Groups: extranet\Webinar
Joined: 7/7/2015(UTC)
Posts: 12

Thanks for all your support. I have also tried with multidimensional subtotals without any luck.
I have now given up, and created a new table in the cube in order to fix my problem.

best regards
Peter
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