BusinessObjects Board

Some sort of SUMIF to filter out batches that total to 0

building reports desktopintelligence
#1

I will attache an excel file to make thing more clear.

On line 2 and 3 I have similar batchnumber but 1 line is positive and the 2nd line is negative.
Together they equal to 0.

I would not want to see this batch because it screws up my Aging because the outgoing (and thus minus) amount was in a different periode and just skips into another Aging bracket.

I was thinking on something like a sumif on batchnumber which would give 0 in both lines.
Then I could filter out all zeros on that field.
Book5.xls (28.0 KB)

rpinxt (BOB member since 2019-01-23)

#2

Well it looked like i fixed it with this variable calles “Test” :

=Sum([Finance Amt Local]) ForEach ([Batch Number]) Where ([Period]<="12/2019")

Now on the lines of batch 7092961 Test showed 0 so that was excelent.

But the field ‘Aging Mths His’ had been taken out.
When I put that field back the Sum did not work anymore :hb:

And I have no clue why because that field has nothing to do with the formula.
Also when I take it out again and put another field in the formulas stays working fine!

But that Aging Mths His field is the field I really need in the report…

Anybody an idea why that field (which is a variable) could impact the SUM formula?

rpinxt (BOB member since 2019-01-23)

#3

Sorry for spamming my own topic but I have to share some more data.

So we have our aging months which is monthsbetween Current date and Batch exp. date.

These months are used to make aging brackets.

Codes are as follows :
UBD Months

=MonthsBetween([Current Date];[Batch Expiration Date])

Aging Mths

=If([UBD Days]<0) Then "A) Expired" ElseIf([UBD Months] Between(0;3)) Then "B) 0-3" ElseIf([UBD Months] Between(4;6)) Then "C) 4-6" ElseIf([UBD Months] Between(7;9)) Then "D) 7-9" ElseIf([UBD Months] Between(10;12)) Then "E) 10-12" ElseIf([UBD Months] Between(13;18)) Then "F) 13-18" ElseIf([UBD Months] Between(19;24)) Then "G) 19-24" ElseIf([UBD Months] > 24) Then "H) >24" Else "Other"

But this aging is ok when you look at it today (at the moment you run the report).
However when you want to put the months next to each other you would need the aging month at the time of end of each months.
Thats why I made Aging Mths His and UBD Months His

The codes for these:
UBD Months His

=MonthsBetween(LastDayOfMonth([Posting Date]);[Batch Expiration Date])

Aging Mths His

=If([UBD Days His]<0) Then "A) Expired" ElseIf([UBD Months His] Between(0;3)) Then "B) 0-3" ElseIf([UBD Months His] Between(4;6)) Then "C) 4-6" ElseIf([UBD Months His] Between(7;9)) Then "D) 7-9" ElseIf([UBD Months His] Between(10;12)) Then "E) 10-12" ElseIf([UBD Months His] Between(13;18)) Then "F) 13-18" ElseIf([UBD Months His] Between(19;24)) Then "G) 19-24" ElseIf([UBD Months His] > 24) Then "H) >24" Else "Other"

So now the SUM formula works just fine with the first 2 dimensions.
But if I put in the ‘His’ dimensions the SUM formula screws up.

None of these dimensions are in the SUM formula but still the last 2 leed to wrong output.
I only could think of the dimension [Posting Date] being of influence here but I would not know how…

rpinxt (BOB member since 2019-01-23)

#4

Once more I am talking to myself unfortunately here on BOB…
Must be doing something wrong.

But perhaps somebody still reads a long a know why I have this problem.

I now found out that when I add posting date to the SUM formula:

=Sum([Finance Amt Local]) ForEach ([Batch Number]) Where ([Period]<="12/2019")

This also makes the forumula screw up.
So looks like when you get more rows the sum function is not working.
Aging Mths His does not give more rows but that variable contains the Posting Date dimension.

So hoping somebody is still reading this and can give me some hint to set me on my way.

rpinxt (BOB member since 2019-01-23)

#5

Hmm think I know why I had so little “fans” in this thread.

Think it is in the wrong section… :oops: :oops:

But for the fans reading along here I did solve the issue.

The sum was ‘wrong’ :

=Sum([Finance Amt Local]) ForEach ([Batch Number]) Where ([Period]<="12/2019")

I replaced it with this and now it works all the time :

=Sum([Finance Amt Local]) In ([Batch Number]) Where ([Period]<="12/2019")

rpinxt (BOB member since 2019-01-23)