BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Sunset Ride 
Sunset Ride 

General Notice: BOB is going to retire...please see details here. All BOB forums have been converted to "read only" as of September 1st.
General Notice: No events within the next 45 days.

Sum of Values between Dates


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
Flooo
Principal Member
Principal Member



Joined: 23 Jan 2013

Posts: 158



PostPosted: Thu Aug 20, 2020 8:42 am 
Post subject: Sum of Values between Dates

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
Back to top
n8aktiv
Principal Member
Principal Member



Joined: 28 Dec 2018

Posts: 150


flag
PostPosted: Thu Aug 20, 2020 1:12 pm 
Post subject: Re: Sum of Values between Dates

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“

_________________
------------------------------------------
SAP BO 4.2 SP6 Patch 10
------------------------------------------
Back to top
Flooo
Principal Member
Principal Member



Joined: 23 Jan 2013

Posts: 158



PostPosted: Fri Aug 21, 2020 1:28 am 
Post subject: Re: Sum of Values between Dates

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
Back to top
n8aktiv
Principal Member
Principal Member



Joined: 28 Dec 2018

Posts: 150


flag
PostPosted: Fri Aug 21, 2020 4:01 pm 
Post subject: Re: Sum of Values between Dates

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 !

_________________
------------------------------------------
SAP BO 4.2 SP6 Patch 10
------------------------------------------
Back to top
Flooo
Principal Member
Principal Member



Joined: 23 Jan 2013

Posts: 158



PostPosted: Sun Aug 23, 2020 6:36 am 
Post subject: Re: Sum of Values between Dates

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
Back to top
n8aktiv
Principal Member
Principal Member



Joined: 28 Dec 2018

Posts: 150


flag
PostPosted: Sun Aug 23, 2020 7:31 am 
Post subject: Re: Sum of Values between Dates

if you want a running sum, then you have to use RunningSum() icon_wink.gif

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

_________________
------------------------------------------
SAP BO 4.2 SP6 Patch 10
------------------------------------------
Back to top
Flooo
Principal Member
Principal Member



Joined: 23 Jan 2013

Posts: 158



PostPosted: Sun Aug 23, 2020 3:09 pm 
Post subject: Re: Sum of Values between Dates

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
Back to top
n8aktiv
Principal Member
Principal Member



Joined: 28 Dec 2018

Posts: 150


flag
PostPosted: Thu Aug 27, 2020 2:12 pm 
Post subject: Re: Sum of Values between Dates

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)

_________________
------------------------------------------
SAP BO 4.2 SP6 Patch 10
------------------------------------------
Back to top
Flooo
Principal Member
Principal Member



Joined: 23 Jan 2013

Posts: 158



PostPosted: Tue Sep 01, 2020 4:59 am 
Post subject: Re: Sum of Values between Dates

Thanks a lot! This solved my problem and is returning the data I was looking for icon_smile.gif

Thank you!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0293 seconds using 17 queries. (SQL 0.0031 Parse 0.0009 Other 0.0253)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo