Search code examples
averagebusiness-objectsdate-difference

Finding the average between two date columns (SAP Business Objects)

I'm looking to have a table display the average tenure of employees by department using SAP Business Objects. For example, all of the employees in Marketing have start and end dates. I first begin by subtracting the latter with the start date to get the number of tenure days.

Where the issue begins is finding the department average number of tenure days and having it display in a table without the employee detail, and then combining departments into offices.

I've tried incorporating sections and creating variables.

=Average(DaysBetween([Begin Date];[End Date]))

Creating a variable that finds the average between two dates defines correctly, however once I incorporate the formula into a table no value appears.

Solution

  • I mocked up a spreadsheet as I understand your situation and created a Web Intelligence report based on that spreadsheet.

    <!DOCTYPE html>
<html>
	<head>
		<meta charset="UTF-8">
			<title>Excel To HTML using codebeautify.org</title>
		</head>
		<body>
			<b>
				<u>Sheet Name</u> :- Sheet1
			</b>
			<hr>
				<table cellspacing=0 border=1>
					<tr>
						<td style=min-width:50px>Employee ID</td>
						<td style=min-width:50px>First Name</td>
						<td style=min-width:50px>Last Name</td>
						<td style=min-width:50px>Begin Date</td>
						<td style=min-width:50px>End Date</td>
						<td style=min-width:50px>Dept ID</td>
						<td style=min-width:50px>Office ID</td>
					</tr>
					<tr>
						<td style=min-width:50px>1</td>
						<td style=min-width:50px>Atticus</td>
						<td style=min-width:50px>Aldridge</td>
						<td style=min-width:50px>05-07-19</td>
						<td style=min-width:50px>07-10-19</td>
						<td style=min-width:50px>1</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>2</td>
						<td style=min-width:50px>Rose</td>
						<td style=min-width:50px>Aldridge</td>
						<td style=min-width:50px>04-16-19</td>
						<td style=min-width:50px>06-26-19</td>
						<td style=min-width:50px>1</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>Thomas</td>
						<td style=min-width:50px>Barrow</td>
						<td style=min-width:50px>01-01-19</td>
						<td style=min-width:50px>08-07-19</td>
						<td style=min-width:50px>2</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>4</td>
						<td style=min-width:50px>Anna</td>
						<td style=min-width:50px>Bates</td>
						<td style=min-width:50px>02-12-19</td>
						<td style=min-width:50px>09-04-19</td>
						<td style=min-width:50px>2</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>5</td>
						<td style=min-width:50px>John</td>
						<td style=min-width:50px>Bates</td>
						<td style=min-width:50px>01-22-19</td>
						<td style=min-width:50px>08-21-19</td>
						<td style=min-width:50px>2</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>6</td>
						<td style=min-width:50px>Phyllis</td>
						<td style=min-width:50px>Baxter</td>
						<td style=min-width:50px>07-16-18</td>
						<td style=min-width:50px></td>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>7</td>
						<td style=min-width:50px>Tom</td>
						<td style=min-width:50px>Branson</td>
						<td style=min-width:50px>03-26-19</td>
						<td style=min-width:50px>06-12-19</td>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>8</td>
						<td style=min-width:50px>Charles</td>
						<td style=min-width:50px>Carson</td>
						<td style=min-width:50px>04-16-19</td>
						<td style=min-width:50px>06-26-19</td>
						<td style=min-width:50px>4</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>9</td>
						<td style=min-width:50px>Elsie</td>
						<td style=min-width:50px>Carson</td>
						<td style=min-width:50px>05-07-19</td>
						<td style=min-width:50px>07-10-19</td>
						<td style=min-width:50px>4</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>10</td>
						<td style=min-width:50px>Richard</td>
						<td style=min-width:50px>Clarkson</td>
						<td style=min-width:50px>08-10-18</td>
						<td style=min-width:50px>03-22-19</td>
						<td style=min-width:50px>4</td>
						<td style=min-width:50px>1</td>
					</tr>
					<tr>
						<td style=min-width:50px>11</td>
						<td style=min-width:50px>Cora</td>
						<td style=min-width:50px>Crawley</td>
						<td style=min-width:50px>01-22-19</td>
						<td style=min-width:50px>05-01-19</td>
						<td style=min-width:50px>1</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>12</td>
						<td style=min-width:50px>Robert</td>
						<td style=min-width:50px>Crawley</td>
						<td style=min-width:50px>01-01-19</td>
						<td style=min-width:50px></td>
						<td style=min-width:50px>1</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>13</td>
						<td style=min-width:50px>Violet</td>
						<td style=min-width:50px>Crawley</td>
						<td style=min-width:50px>03-05-19</td>
						<td style=min-width:50px></td>
						<td style=min-width:50px>2</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>14</td>
						<td style=min-width:50px>Daisy</td>
						<td style=min-width:50px>Mason</td>
						<td style=min-width:50px>05-02-18</td>
						<td style=min-width:50px>07-13-19</td>
						<td style=min-width:50px>2</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>15</td>
						<td style=min-width:50px>Isobel</td>
						<td style=min-width:50px>Merton</td>
						<td style=min-width:50px>03-26-19</td>
						<td style=min-width:50px>06-12-19</td>
						<td style=min-width:50px>2</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>16</td>
						<td style=min-width:50px>Joseph</td>
						<td style=min-width:50px>Molesley</td>
						<td style=min-width:50px>02-16-18</td>
						<td style=min-width:50px></td>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>17</td>
						<td style=min-width:50px>Andy</td>
						<td style=min-width:50px>Parker</td>
						<td style=min-width:50px>06-21-18</td>
						<td style=min-width:50px>09-15-19</td>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>18</td>
						<td style=min-width:50px>Beryl</td>
						<td style=min-width:50px>Patmore</td>
						<td style=min-width:50px>05-28-19</td>
						<td style=min-width:50px>07-24-19</td>
						<td style=min-width:50px>4</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>19</td>
						<td style=min-width:50px>Bertie</td>
						<td style=min-width:50px>Pelham</td>
						<td style=min-width:50px>04-07-18</td>
						<td style=min-width:50px>06-11-19</td>
						<td style=min-width:50px>4</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>20</td>
						<td style=min-width:50px>Edith</td>
						<td style=min-width:50px>Pelham</td>
						<td style=min-width:50px>03-05-19</td>
						<td style=min-width:50px>05-29-19</td>
						<td style=min-width:50px>4</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>21</td>
						<td style=min-width:50px>Lucy</td>
						<td style=min-width:50px>Smith</td>
						<td style=min-width:50px>05-27-18</td>
						<td style=min-width:50px>08-14-19</td>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>22</td>
						<td style=min-width:50px>Henry</td>
						<td style=min-width:50px>Talbot</td>
						<td style=min-width:50px>03-13-18</td>
						<td style=min-width:50px>05-10-19</td>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>2</td>
					</tr>
					<tr>
						<td style=min-width:50px>23</td>
						<td style=min-width:50px>Mary</td>
						<td style=min-width:50px>Talbot</td>
						<td style=min-width:50px>02-12-19</td>
						<td style=min-width:50px>05-15-19</td>
						<td style=min-width:50px>3</td>
						<td style=min-width:50px>2</td>
					</tr>
				</table>
				<hr>
				</body>
			</html>

    I created two variables with their Qualification set to "Measure"...

    Tenure=DaysBetween([Begin Date]; If(IsNull([End Date]);CurrentDate();[End Date]))

Avg Tenure=Average([Tenure])

    Note that if End Date is not defined then I am subtracting Begin Date from CurrentDate(). It seems to work for me...

    enter image description here

    Does that work for you? If not, what am I missing?