Welcome Guest! To enable all features please Login or Register.
Go to last post Go to first unread
Rasmus Remmer Nielsen  
#1 Posted : Thursday, June 16, 2011 11:03:22 AM(UTC)
Rasmus Remmer Nielsen
Rank: Advanced Member

Groups: Administrators
Joined: 6/14/2011(UTC)
Posts: 22


I have built an analysis of financial supplier transactions for use in Accounts Payable. There is one measure which is divided using one comparison with four members;

1. Balance at start of period
2. Debit Transactions in period
3. Credit Transactions in period
4. Balance at end of period

However, I wish to exclude (hide) suppliers where all four are either zero or null - but I want to include those that might have zero balance at both start and end, yet contain both payment and invoice transaction values.

I found a way to do this by making a custom calculation that reads like this; square of 1 + square of 2 + square of 3 + square of 4 - if the sum of squares is 0 exclude the member. I am using the square of the number to make sure that any value gets reported and not hidden accidentally if, say 2 and 3 are the same figure with different signs, they would cancel each other out and the sum of non-squared members would be 0.

Is there any way in TargIT to use the equivalent of MS Excel ABS function instead of squaring (to ease the reading of the formula)?

It works well in most cases, but sometimes the data in the cube is wrong on the 10th or later decimal, not something to worry about normally, but this makes the report show those zeros (when rounded to 2 or 0 decimals) in spite of the exclusion.

I got around this by adjusting the criteria for hiding a member to value<0.01 (rather than value=0)

It's not really foolproof, so I was wondering if there was a way to round numbers in TargIT calculations equivalent to MS Excel ROUND function?


Edited by user Thursday, June 16, 2011 11:04:29 AM(UTC)  | Reason: Not specified

#2 Posted : Monday, June 27, 2011 7:26:38 AM(UTC)
Rank: Administration

Groups: Administrators
Joined: 4/7/2011(UTC)
Posts: 323

Hi Rasmus,

It is correct that the TARGIT calculation engine does not have the ABS nor ROUND functions. So I think your solution is the best available under the given circumstances.

Especially I think the "trick" with using squares to avoid zero sums due to real numbers negating each other out is something that may be useful to many others.

For those interested in using squares, the syntax would be like this: sum(d1, 0, m1)^2.

And, by the way, square roots: sum(d1, 0, m1)^0.5.

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
Users browsing this topic
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.