en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
Alexandra Hoppe  
#1 Posted : Wednesday, August 21, 2013 11:42:08 PM(UTC)
Alexandra Hoppe
Rank: Newbie

Groups: extranet\Forum
Joined: 8/21/2013(UTC)
Posts: 3

Hi all,

I have a problem with a calculation using a relative reference. The calculation is defined like this:

sum(0,@"[Saphir GmbH]",m1) % sum(-3,@"[Saphir GmbH]",m1)

In case of calculating the first column the reference to "column -3" shows an "not defined". That is absolut correct but I want to hide this information like I do it with an error in calulation resulting from a division by 0 over the agent.
But I find no solution for this problem. Is there anybody with a great idea?

Thank you and best regards
Alexandra Hoppe
Páll Bjarkason  
#2 Posted : Thursday, August 22, 2013 9:28:46 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

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

Dear Alexandra

It is possible to hide a range of calculated column on the Format visibility tab.

Properties -> Visibility -> Hide a range of Month ”-3 column”

Format visibility and choose the ranges shown in the picture.
UserPostedImage


The result can be seen here. I.e. the "-3 column" is hidden in Jan, Feb and Mar.
UserPostedImage

Edited by user Thursday, August 22, 2013 9:29:45 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
Alexandra Hoppe  
#3 Posted : Thursday, August 22, 2013 11:39:28 AM(UTC)
Alexandra Hoppe
Rank: Newbie

Groups: extranet\Forum
Joined: 8/21/2013(UTC)
Posts: 3

Hi Páll,

thank you for your quick answer. Unfortunately it doesn't solve my problem.
It will work if I define the calcualtion in columns for every month.
But I have only one calulated row.
In your example:
I need a calculated row for Alvaro Bennett wich divide the revenue of Alvaro Bennett for every month by the revenue three month before. So I have to hide only the cell content for Jan, Feb and Mar because of the "undefined".

I hope you can provide a solution.

Thank you
Alex
Niels Thomsen  
#4 Posted : Thursday, August 22, 2013 1:17:26 PM(UTC)
Niels Thomsen
Rank: Advanced Member

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

Hi Alex

I suggest that you utilize the 4th parameter in the TARGIT syntax.

Your formula: sum(-3,@"[Saphir GmbH]",m1) can be extended a little like this: sum(-3,@"[Saphir GmbH]",m1, 0).

The 4th parameter (the zero after the measure) simply means that if the reference turns out to be invalid (undefined), the value that is mentioned as the 4th parameter (in this case 0) should be used as a substitute for the "unreachable" value.

In your case the value 0 is probably not a good idea since you would then be dividing by zero, but you can put in any value as the 4th parameter - and even a full expression like this: sum(-3,@"[Saphir GmbH]",m1, sum(0,@"[Saphir GmbH]",m1)).

Again that is not to meaningfull in your case since that syntax says: In case the column relatively 3 to the left of the current is not available then just use the current.

In your case however I would test if the value I wish to use is available and then perform two different actions depending on the answer:

if sum(-3,@"[Saphir GmbH]",m1, -999999999) = -999999999
then sum....
else
sum...

Hope it makes sense.

Hope it makes sense.

Best Regards
Niels Thomsen

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


Alexandra Hoppe  
#5 Posted : Thursday, August 22, 2013 2:04:03 PM(UTC)
Alexandra Hoppe
Rank: Newbie

Groups: extranet\Forum
Joined: 8/21/2013(UTC)
Posts: 3

Hi Niels,

perfect, that works!

Many thanks for your input.

Best Regards
Alex
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