en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Antonio Dubravcic  
#1 Posted : Thursday, August 9, 2012 6:24:52 PM(UTC)
Antonio Dubravcic
Rank: Newbie

Groups: extranet\Forum
Joined: 8/8/2012(UTC)
Posts: 2

I need to apply a different calculation formula depending on the result of a conditional statement based on a dimension value not on a measure value. See Example below

Dim M1 C1
A 80 M1 *.7
B 70 M1 *.6
C 85 M1 *.6
D 60 M1 *.7
E 80 M1 *.6

This needs to be reliable even with hierarchies. In the example above my basic C1 formula changes for A and D so it should go along these lines IF Dim="A" or Dim="D" THEN M1*.7 ELSE M1*.6
I have used MDX in the past to solve this and I have also used workarounds in Targit of this sort "if sum(d1, @"[A]", m1) = sum(d1, 0, m1) then" or evaluating the count " if count(d1,d1:0,m1)=1 or 4 in this case" and many other combinations (i.e. using levels for hierarchies, count comparisons, etc...)but I would like to know if something more independent of the measure is available because counts can change and measures can have the same values which will make my workarounds useless. I am sure there is and I just have not found it yet.

Thanks for any help

Antonio

Ole  
#2 Posted : Friday, August 17, 2012 12:03:50 PM(UTC)
Ole
Rank: Administration

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

Hi Antonio,

My approach will still be using a count function, like "if allcount(d1, d1:0, m1) = allcount(d1, d1:@"[A]", m1) then ...".

See the screenshot example where I have used a syntax that will work on the top level of the hierarchy, no matter how the deep the hierarchy might be.

UserPostedImage

Best regards,
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
Antonio Dubravcic  
#3 Posted : Wednesday, August 22, 2012 11:39:45 PM(UTC)
Antonio Dubravcic
Rank: Newbie

Groups: extranet\Forum
Joined: 8/8/2012(UTC)
Posts: 2

Thanks Ole for the answer however that solution made me run into another issue. When i use the formula you recommended I am no longer able to calculate intersections with other calculations i may have on the sheet and that is really frustrating. any help would be appreciated.

Antonio
Ole  
#4 Posted : Thursday, August 30, 2012 11:58:00 AM(UTC)
Ole
Rank: Administration

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

That is true. The count function will be able to count dimension members only - and since you have no dimension members in e.g. a calculated Average row, it won't work. However, you *will* be able to do an intersection calculation the other way around, e.g. an Average on your count calculation. Not sure if this will be helpful to you.
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
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