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

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: Upcoming Events: PGHBOUG: Aug 23.

[BOBJ 4.x] Calculating "Cycle Time" using =DaysBetween


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



Joined: 14 Oct 2014

Posts: 11



PostPosted: Wed Jul 11, 2018 7:10 am 
Post subject: Calculating "Cycle Time" using =DaysBetween

Work ID |Status........|DateInStatus...| Start Date...| End Date | Cycle Time
B-42341 | Analyze.......| 5/17/2017
B-42341 | Ready.........| 5/29/2017
B-42341 | In Progress..| 6/8/2017........| 6/8/2017
B-42341 | Done...........| 7/7/2017
B-42341 | Testing........| 8/8/2017
B-42341 | Accepted......| 8/11/2017......|...............| 8/11/2017

I'm trying to calculate the Cycle Time between the time cards go from "In Progress" to "Accepted". Cycle Time variable returns nothing because most of the values are NULL for Start Date and End Date.

Cycle Time = DaysBetween([Start Date];[End Date])
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11011
Location: Manchester, UK


flag
PostPosted: Thu Jul 12, 2018 3:36 am 
Post subject: Re: Calculating "Cycle Time" using =DaysBetween

So in your example, would it be In Progress Start Date to Accepted End Date?

If so, try this:

Code:
=DaysBetween(Min([Start Date]) Where ([Status]="In Progress");Max([End Date]) Where ([Status]="Accepted"))

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
mrodenkirch
Forum Member
Forum Member



Joined: 14 Oct 2014

Posts: 11



PostPosted: Thu Jul 12, 2018 7:59 am 
Post subject: Re: Calculating "Cycle Time" using =DaysBetween

Yes...In-Process = [Start Date] and Accepted = [End Date]

The Cycle Time is the number of days from "In-Process" until the day it arrives in an "Accepted" state.

Your formula is returning a blank in my report. It validates fine, but is not showing anything when I drag it into the table. It shows #MULTIVALUE when I drag it alone in a blank report.

I'm also confused why you are using Max and Min since there is only one start and end date, but you have the right idea about wanting the days between the start and end dates.

It seems if I have both a valid start date and end date in the same row the DaysBetween formula works (when I do it manually with dummy variables).

In the real report "In Process" start date is in one row and the "Accepted" end date is in a different row. I think the DaysBetween formula gets confused with the blank start and/or end entries in the same row.

Start Date = [First Date In Status] Where ([Status]="In Progress")
End Date = [First Date In Status] Where ([Status]="Accepted")
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 11011
Location: Manchester, UK


flag
PostPosted: Mon Jul 16, 2018 3:22 am 
Post subject: Re: Calculating "Cycle Time" using =DaysBetween

Max and min allow me to "flatten" all the rows that relate to a particular work ID.

The best way to get these working is generally to build up your report table with the components that you want.

Start with [Work ID].
Then add [Start Date] Where ([Status] = "Accepted" - you'll get multiple rows I'd expect, hence the need to add a Min to flatten it.

_________________
Current version I'm using: 4.1 SP3 over Oracle. Well versed in SQL Server too, including SSIS

______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Desktop 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 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0333 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0295)
CCBot/2.0 (http://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