Sum of data that is repeated

building reports desktopintelligence
1

Hi all, first time post so apologies.
I have data showing an account reference, then all the transactions against that reference - only 5 or six, then for each row it will show the same balance calculated last Sunday.
So my table looks like this:
Ref 123456
Transaction Code 1
Transaction Credit 45
Transaction Debit
Balance 2300

Then imagine multiple rows with the same Ref same Balance, but different Transaction Code, some blank Credit, and figures in the Debit column.

Then the same but for multiple References.

Now I want to have one balance per account and ignore the credit and debits, how do I force it to SUM 1 Balance per Ref?

I hope that is clear enough, thanks in advance for your help

ihardingham2 (BOB member since 2016-03-30)

2

Hello

You may not have had a reply because you’re posting in Deski - are you using Deski or Webi?

3

Hi, yes this is a Deski version.

This is a general issue that I come across on a lot of my reports, the summary pages dont quite add up to the details.

I have found ways to work around the problem but that involves creating flags and counting them, it is long winded and poor practice so I am hoping you can help out here.

thanks
Ian

ihardingham2 (BOB member since 2016-03-30)

4

It’s not something stupidly simply like:

=Max() in ()

is it? Just wondering if there is one balance pulled back and it is repeating, is it simply generating a Cartesian display and applying the same balance to each line in Ref.

Or did you mean that you wanted an alerter to blank out the previous rows?

Sorry, Ian, but you haven’t made the end requirement clear. :frowning:

5

Hi,

I found that using the "sum(variable) ForEach "was something that worked to ensure that this provided me a summary.

However I can extend the question more if people are happy to help. If I calculate the sum of balances on accounts using the SUM function, it add’s positives and removes negatives, if I am looking at an arrears figure I need the total arrears, not the total arrears minus anything in credit. How would I write a formula to do that?

E.g. Balances as follows:
£300
£400
-£500

Total Arrears is some of balances: £700
The -£500 is an account in credit by that much.

Thanks in advance

ihardingham2 (BOB member since 2016-03-30)

6

Some general pseudo code for you to apply:

=Sum(If( >0 Then Else 0))

7

Hi MarkP

Thanks for that formula, it is almost identical to the Case Statement I wrote in Oracle to give me the flag in which to use.

It hasn’t worked just yet but I am doing something wrong, I understand the logic behind so will keep experimenting with something along the lines of what you gave me.

I appreciate the time and all the reads, I will learn to use BO by the time we decide to not use it any more!

Quick Question, does the more modern version of Webi allow SQL source for reports? As opposed to the drag and drop versions using universes? We have not upgrading for exactly that reason.

Cheers all

ihardingham2 (BOB member since 2016-03-30)

8

Yes.

You can do it in Webi from the start with custom queries - having dummy objects of each type will allow you do it. For the pure custom sql solution, 4.1 SP6 and upwards is what you need.