BusinessObjects Board

Sum of Values between Dates

building reports webintelligence
#1

Hi Members,

I need your help on below issue.
I have a report, returning the Billing Date and the quantity and I created my own variable KE Wk Range; basically adding 7 days to the billing date.

Now I want to see the total quantity between these days.
My report is showing the billing dates, the KE Wk Range as dates.

I am adding below, however I am getting the total qty and not only the quantity for the next 7 days for each billing date record.

=Sum([NR Tot Qty]) Where ([Billing Date] Between ([Billing Date]; [KE Wk Range]))

Could you please advise here?

Thanks
Flooo

Flooo (BOB member since 2013-01-23)

#2

make sure that all date-fields are formatted correctly as date.
test every single comparing-step with a simplified formula
and try using <= and >= instead of “between”

n8aktiv :de: (BOB member since 2018-12-29)

#3

I was poking around with that earlier and its not working.
its still returning the total of table.
I also added foreach at the end.

=Sum([NR Tot Qty]) Where ([Billing Date] >= [Billing Date] And [Billing Date] <= [KE Wk Range])

=Sum([NR Tot Qty]) Where ([Billing Date] >= [Billing Date] And [Billing Date] <= [KE Wk Range]) ForEach ([Billing Date])

I also added a screenshot of my report.

Any further ideas?
Thanks in advance
Floo
BoB.jpg

Flooo (BOB member since 2013-01-23)

#4

every row in your example is matching the conditions so total is correctly summed up !

[Billing Date] = [Billing Date] (whatever that is supposed to do ?!)
and
[Billing Date] < [KE Wk Range]

you have to rethink your logic !

n8aktiv :de: (BOB member since 2018-12-29)

#5

In the end I would like to get the running sum value up to the date of the KE we range for each billing date.

Is this possible?

Thanks
Flooo

Flooo (BOB member since 2013-01-23)

#6

if you want a running sum, then you have to use RunningSum() :wink:

with this syntax:
RunningSum([measure];[Row|Col];[(reset_dimensions)])

n8aktiv :de: (BOB member since 2018-12-29)

#7

Unfortunately it’s not working how I am using it.

For the billing date 12/1 I want to get sum of all values from 12/1 until 12/8 returned in the same line, 139.2 Mu.
For 12/2, the total from 12/2 until 12/9, 146.5 Mu

Thanks
Flooo

Flooo (BOB member since 2013-01-23)

#8

if there is always the same 7days-distance you can try this:

=RelativeValue([NR Tot Qty];([Billing Date]);1)+
RelativeValue([NR Tot Qty];([Billing Date]);2)+
RelativeValue([NR Tot Qty];([Billing Date]);3)+
RelativeValue([NR Tot Qty];([Billing Date]);4)+
RelativeValue([NR Tot Qty];([Billing Date]);5)+
RelativeValue([NR Tot Qty];([Billing Date]);6)+
RelativeValue([NR Tot Qty];([Billing Date]);7)

n8aktiv :de: (BOB member since 2018-12-29)

#9

Thanks a lot! This solved my problem and is returning the data I was looking for :slight_smile:

Thank you!

Flooo (BOB member since 2013-01-23)