en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Weber Stefan  
#1 Posted : Friday, October 31, 2014 2:42:06 PM(UTC)
Weber Stefan
Rank: Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 7

Hi

I want to create an accumulated sum for the second Dimension. That means the counter shoud start again when the dimension article changes.
(With the calculation 'sum(d-1, d1:0, m1)' I get the accumulated sum from beginning to the end of the table)

See Picture:

http://de.tinypic.com/view.php?pic=2lxffyx&s=8

Thanks for your help!

Stefan

Edited by user Friday, October 31, 2014 3:44:47 PM(UTC)  | Reason: Not specified

Lotte Bjørn Jensen  
#2 Posted : Thursday, November 6, 2014 2:16:18 PM(UTC)
Lotte Bjørn Jensen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 4/8/2011(UTC)
Posts: 68

Hi Stefan

I think this would do it: sum(d-1, d1:0(s), 0)
Best regards

Lotte Bjørn
Weber Stefan  
#3 Posted : Monday, November 10, 2014 8:42:13 AM(UTC)
Weber Stefan
Rank: Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 7

Hi Lotte

Thanks that works.
I tried now to add an additional Dimension but then it's wrong again. I should have the possibility to define on which level the Sibling is specified. I tried with l1 (level1) for example but this doesn't help.
Do you have an idea?

http://de.tinypic.com/view.php?pic=28vzsd4&s=8

Best regards
Stefan
Ole  
#4 Posted : Tuesday, November 11, 2014 11:30:23 AM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 324

Hi Stefan,

The problem with the siblings (s) modifier is that it is generally only good at working at lowest level of a hierarchy.

In your case, where you have a three-level dimension hierarchy on the vertical axis you achieve the desired result by applying two calculated columns:

Calc1: count(d1, d1:0(l(1,0,0)), m1)
This will make an accumulated count of dimension members on the first level of the hierarchy, and will later be used to determine when we have a shift from one dimension member to the next (on the top level).

Accumulated sum: if sum(c1, 0, 0) <> sum(c1, -1, 0, 0) then sum(d1, 0, m1) else sum(c2, -1, 0, 0) + sum(d1, 0, m1)

Please try it and let me know if it works in your case.

BR / Ole

Edited by user Tuesday, November 11, 2014 11:30:57 AM(UTC)  | Reason: Not specified

I work as an employee with TARGIT A/S. 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 TARGIT A/S.

UserPostedImage Ole Dyring
Weber Stefan  
#5 Posted : Wednesday, November 12, 2014 3:48:07 PM(UTC)
Weber Stefan
Rank: Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 7

Hi Ole

It's magic! It works perfect. Thanks very much!
Could you explain me a little bit what means "else sum(c2, -1, 0, 0)". I'm wondering why c2 and what is the fourth parameter.

Best regards
Stefan
Ole  
#6 Posted : Thursday, November 13, 2014 8:33:31 PM(UTC)
Ole
Rank: Administration

Groups: extranet\Forum
Joined: 4/7/2011(UTC)
Posts: 324

Hi Stefan,

The sum(c2, -1, 0, 0) explained...

The first part of the reference syntax is the column reference. I have added two calculated columns to the crosstab which can be referred to as "c1" and "c2" respectively. In this case the "c2" reference is in fact a reference to the column itself!

The second part of the reference syntax is the row reference. The "-1" is a relative reference - in this case to "the previous row".

The third part of the reference syntax is the measure reference. The "0" is also a relative reference - in this case meaning "the current measure". I could have used "m1" instead.

The fourth (optional) part of the reference syntax is the "undefined value" reference. In this particular case the "previous row" reference, mentioned above, will at least once hit an "undefined value", and that is when doing the calculation on the very first row (there is no row before the first row). In that case the fourth parameter substitutes the otherwise undefined state. So here, instead of getting "undefined" for the first row, we substitute it with "0".

BR / Ole
I work as an employee with TARGIT A/S. 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 TARGIT A/S.

UserPostedImage Ole Dyring
Weber Stefan  
#7 Posted : Friday, November 14, 2014 6:34:39 AM(UTC)
Weber Stefan
Rank: Member

Groups: extranet\Forum
Joined: 4/5/2011(UTC)
Posts: 7

Thanks very much for the professionel help!

Best regards
Stefan

Edited by moderator Tuesday, November 25, 2014 11:34:27 AM(UTC)  | Reason: Not specified

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