en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Alastair Dinning  
#1 Posted : Wednesday, October 26, 2011 8:22:24 PM(UTC)
Alastair Dinning
Rank: Newbie

Groups: extranet\Customer
Joined: 4/14/2011(UTC)
Posts: 2

I'm trying to get TargIT to return an empty cell under certain conditions, but I can't see how to give me an null value

For example, I have 3 customers, i know the revenue and cost, so I want to calculate the margin, however, one customer has neither revenue or costs.


I want to say:
if Rev = "" then "" else Rev - cost
but "" is not working. Right now Targit is returning a 0 where there is no revenue, and that's not helping other calculations.
Ole  
#2 Posted : Wednesday, November 9, 2011 8:55:00 AM(UTC)
Ole
Rank: Administration

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

Unfortunately the TARGIT calculation syntax is only capable of returning numeric values, e.g. "0". So even if you are referring to a null value, the claculations syntax will read/write this as a zero.

If the purpose is to leave the resulting cell blank to the end user, you may consider using a visibility agent to "hide value when" e.g. sum(c1, 0, m1)=0.
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
Rasmus Remmer Nielsen  
#3 Posted : Friday, November 11, 2011 12:28:37 PM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: extranet\Customer
Joined: 6/14/2011(UTC)
Posts: 22

Hi Alistair,

I had a similar problem a while ago. It was solved by adding a calculated measure to the cube itself. I think it is simpler than using the calculations inside TargIT, and if you add it to the cube, it will be available for all your analyses and reports and not just the one in which you created the calculation.

In Analysis Services->Cube->Script Organizer; right click to add a new "Calculated Member". In the calculation definition, you should be able to add something like this:

Code:
[Measures].[Revenue]-[Measures.Cost]


Remember that name should be enclosed in square brackets if it contains spaces (I just always enclose them in square brackets out of habit), could be something like this: [Gross Margin]
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