en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Jens Henrik Rauff Hansen  
#1 Posted : Friday, November 16, 2012 9:37:05 AM(UTC)
Jens Henrik Rauff Hansen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 11/16/2012(UTC)
Posts: 24

Hi,

I want to sum up values beween two dimension values, in this case:

sum(@"[12]":@"[30]", 0, m1)

But the value of @"[30]" i not always present, in my case only @"[27]" i present.

Thanks in advance
-Jens Henrik
Ole  
#2 Posted : Friday, November 16, 2012 9:42:11 AM(UTC)
Ole
Rank: Administration

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

I am not sure this will work - it requires that the first formula returns "undefined" when @"[30]" is not present.

Try this: sum(@"[12]":@"[30]", 0, m1, sum(@"[12]":@"[27]", 0, m1))

Notice that the formula syntax now contains four entries. The fourth entry could be a fixed number, e.g. "0", or as in this case a new formula. The fourth entry comes into action when the first part returns "undefined".

PS: If @"[27]" returns undefined as well you can of course add a fourth entry to that part of the formula syntax and so on.

Edited by user Friday, November 16, 2012 9:43:22 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
Jens Henrik Rauff Hansen  
#3 Posted : Friday, November 16, 2012 10:39:10 AM(UTC)
Jens Henrik Rauff Hansen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 11/16/2012(UTC)
Posts: 24

Thansk for the fast reply,

sum(@"[12]":@"[30]", 0, m1, sum(@"[12]":@"[27]", 0, m1)) does not work, is goes "Not defined".
also by tryin just to insert 1 by: sum(@"[12]":@"[30]", 0, m1, 1) does not work, it returns "Not defined".

I am running 2k11 SR3
Niels Thomsen  
#4 Posted : Friday, November 16, 2012 11:15:52 AM(UTC)
Niels Thomsen
Rank: Advanced Member

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

I have tested a little on a simular situation in demodata and believe there is a "nerdy" explanation as well as a solution.

The problem is that Not defined seems to have two different meanings within the client.
If you reference a scope like sum(@"[12]":@"[30]", 0, m1) it will return Not defined when the scope @"[12]":@"[30]" is completely or partly missing but the 4th parameter will not be activated.

However if you reference just one dimension value like @"[30]" - could be in a formula like this: sum(@"[30]",0,m1,1) - the 4th parameter (in this case the value 1) will be returned if the dimension value isn't there.

By creating this formula we have actually set a flag stating @"[30]" is missing - now we can make a conditional calculation like this:
if sum(c1,0,m1) = 1 then sum(@"[12]":@"[27]", 0, m1) else sum(@"[12]":@"[30]", 0, m1)
Hope it makes sense.

Best Regards
Niels Thomsen

https://dk.linkedin.com/in/ncthomsen


Jens Henrik Rauff Hansen  
#5 Posted : Friday, November 16, 2012 11:35:56 AM(UTC)
Jens Henrik Rauff Hansen
Rank: Advanced Member

Groups: extranet\Forum
Joined: 11/16/2012(UTC)
Posts: 24

Thanks Niels

In my case i never know if anny of the dimension will appear, so my solution was this:

(sum(@"[12]", 0, m1, 0) + sum(@"[13]", 0, m1, 0)
+ sum(@"[14]", 0, m1, 0) + sum(@"[15]", 0, m1, 0)
+ sum(@"[16]", 0, m1, 0) + sum(@"[17]", 0, m1, 0)
+ sum(@"[18]", 0, m1, 0) + sum(@"[19]", 0, m1, 0)
+ sum(@"[20]", 0, m1, 0) + sum(@"[21]", 0, m1, 0)
+ sum(@"[22]", 0, m1, 0) + sum(@"[23]", 0, m1, 0)
+ sum(@"[24]", 0, m1, 0) + sum(@"[25]", 0, m1, 0)
+ sum(@"[26]", 0, m1, 0) + sum(@"[27]", 0, m1, 0)
+ sum(@"[28]", 0, m1, 0) + sum(@"[29]", 0, m1, 0)
+ sum(@"[30]", 0, m1, 0))

Thanks for the help
-Jens henrik
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