Calculated field question sum of a product rather then the product of sums

Here is my calculated field. I want to sum the “effective duration” * “portfolio weight” but what I get back is the sum of the “effective duration” * sum of “portfolio weight”.

How would I write that code or should I do that calculation when I am importing the data into the fact table.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration]
AS Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Effective Duration]
)
Then Null
When IsEmpty
(
[Measures].[Portfolio Weight]
)
Then Null
Else (
// The Root function returns the (All) value for the target dimension.
[Measures].[Effective Duration] * [Measures].[Portfolio Weight]
)

End,
FORMAT_STRING = "#,#.0000",
VISIBLE = 1 ;

I believe the problem is caused by the fact that calculated measure does not have an aggregation function by itself.

At each level in a user hierarchy, it will have to compute its value based on the values of underlying regular measures you supplied in the expression. Hence, since in most likelihood 'effective duration' and 'portfolio weight' are aggregated using Sum function, this explains the result you are getting.

You may want to specify how to compute the calculated measure for each level of the 'target hierarchy'.

The following example does that using recursive definition for Weighted Duration. It will look at whether currentmember is a leaf in the 'target hierarchy' or not.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration] AS

IIF ( IsLeaf([Target Dim].[Target Hierarchy].CurrentMember),

[Measures].[Effective Duration] * [Measures].[Portfolio Weight]),

Sum(([Target Dim].[Target Hierarchy].CurrentMember.Children, [MEASURES].[Weighted Duration])))

|||

I think this is the right concept but I need to go all the way down to the fact table. Either that or I do it in the FactTable itself. I feel like that's somewhat of a shortcut. If I precalculate everything in the fact table during the data import I feel like I'm cheating a little. I thought olap was made for this sort of thing. Weightings/ratios are rather basic.

The other part about it is pulling out a measure for a specific level in the heirarchy. No matter what dimensions I am using, if I am using DimPortfolio, I want the total of a given measure (market value) for the "All" level of the portfolio. I'm having trouble with that too.

|||

Precalculating is the best solution, given some limitations that calculated members/measures have.

You can submit your wishlist to SQL Server group though, I believe I saw some website dedicated to that, https://connect.microsoft.com

Calculated field question sum of a product rather then the product of sums

Here is my calculated field. I want to sum the “effective duration” * “portfolio weight” but what I get back is the sum of the “effective duration” * sum of “portfolio weight”.

How would I write that code or should I do that calculation when I am importing the data into the fact table.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration]
AS Case
// Test to avoid division by zero.
When IsEmpty
(
[Measures].[Effective Duration]
)
Then Null
When IsEmpty
(
[Measures].[Portfolio Weight]
)
Then Null
Else (
// The Root function returns the (All) value for the target dimension.
[Measures].[Effective Duration] * [Measures].[Portfolio Weight]
)

End,
FORMAT_STRING = "#,#.0000",
VISIBLE = 1 ;

I believe the problem is caused by the fact that calculated measure does not have an aggregation function by itself.

At each level in a user hierarchy, it will have to compute its value based on the values of underlying regular measures you supplied in the expression. Hence, since in most likelihood 'effective duration' and 'portfolio weight' are aggregated using Sum function, this explains the result you are getting.

You may want to specify how to compute the calculated measure for each level of the 'target hierarchy'.

The following example does that using recursive definition for Weighted Duration. It will look at whether currentmember is a leaf in the 'target hierarchy' or not.

CREATE MEMBER CURRENTCUBE.[MEASURES].[Weighted Duration] AS

IIF ( IsLeaf([Target Dim].[Target Hierarchy].CurrentMember),

[Measures].[Effective Duration] * [Measures].[Portfolio Weight]),

Sum(([Target Dim].[Target Hierarchy].CurrentMember.Children, [MEASURES].[Weighted Duration])))

|||

I think this is the right concept but I need to go all the way down to the fact table. Either that or I do it in the FactTable itself. I feel like that's somewhat of a shortcut. If I precalculate everything in the fact table during the data import I feel like I'm cheating a little. I thought olap was made for this sort of thing. Weightings/ratios are rather basic.

The other part about it is pulling out a measure for a specific level in the heirarchy. No matter what dimensions I am using, if I am using DimPortfolio, I want the total of a given measure (market value) for the "All" level of the portfolio. I'm having trouble with that too.

|||

Precalculating is the best solution, given some limitations that calculated members/measures have.

You can submit your wishlist to SQL Server group though, I believe I saw some website dedicated to that, https://connect.microsoft.com

Calculate duration whilst excluding non working time

HI

I have a helpdesk application and would like to calculate the duration of a call excluding non working time.

I already have a calendar table which lists working dates/times and a function as follows:

CREATETABLE [dbo].[iHLPWorkingHours] (

[workfromdt] [datetime] NOTNULL,

[worktodt] [datetime] NOTNULL

Sample data:

WorkFromDt WorkToDt

02/01/2007 08:00:00 02/01/2007 18:00:00
03/01/2007 08:00:00 03/01/2007 18:00:00
04/01/2007 08:00:00 04/01/2007 18:00:00
05/01/2007 08:00:00 05/01/2007 18:00:00
06/01/2007 06/01/2007
07/01/2007 07/01/2007

Non-working days such as weekends and holidays have their times removed in the iHLPWorkingHours table.

To calculate the call duration I use the following function:

CREATE FUNCTION dbo.TotalCallDuration
(
@.fromdt DATETIME,
@.todt DATETIME
)
RETURNS INT

AS

BEGIN

RETURN
(
SELECT CAST((SUM(DATEDIFF(MINUTE,workfromdt,worktodt)) -
DATEDIFF(MINUTE,MIN(workfromdt),@.fromdt) -
DATEDIFF(MINUTE,@.todt,MAX(worktodt))) AS DECIMAL(9,2))
AS working_hours
FROM ihlpWorkingHours
WHERE NOT (@.fromdt >= worktodt OR @.todt <= workfromdt)
HAVING MIN(workfromdt) <= @.fromdt AND MAX(worktodt) >= @.todt
)
END

Then pass in the opening and closing dates from the Helpdesk Call to the function to calculate the duration in minutes:

select callid, openeddatetime,
closeddatetime, dbo.TotalCallDuration(openeddatetime, closeddatetime) as duration
from ihlpcall
statusid='closed'

This works fine when a call has been opened or closed within working hours M-F but for calls that have been opened or closed outside of these times (after 18:00 and before 08:00) M-F or Weekends the function returns a null value for the call duration.

Is there any way the function can be altered to compensate for calls opened or closed outside of working hours.

Thanks in advance.

Paul

I think this will give you the logic you need. The nested query builds a set of all to-from ranges valid for the query. It corrects the from and to dates when a full date hasn't expired. It then calculates the minutes in the ranges and sums them.

There are some more elegant solutions to this, but I think this is probably the most readible.

Please note, if a call is opened and closed outside working hours without spanning a working period, this will still return NULL.

Code Snippet

select sum(datediff(minute, x.fromdt, x.todt))
from (
select
case
when @.fromdt >= workingfromdt then @.fromdt
else workingfromdt
end as fromdt,
case
when @.todt <= workingtodt then @.todt
else workingtodt
end as todt
from ihlpworkinghours
where workingtodt >= @.fromdt AND
workingfromdt <= @.todt
) x

|||

Hi Brian,

many thanks for your response, I have tried pasting the code snippet into my function but it fails the sysntax check, with the following error:

Error 1075: RETURN statements in scalar valued functions must include an argument

am I missing something?

Thanks

Paul

|||

If you are using the code in a function, the function must return some value. Declare a variable of an appropriate type, assign the results of the SELECT statement to that variable, and then return the variable.

To keep things simple, I recommend just testing the results as a simple SELECT statement, verify it's accurate, and then work on migrating it to a function.

Good luck,
Bryan

sql

Calculate duration whilst excluding non working time

HI

I have a helpdesk application and would like to calculate the duration of a call excluding non working time.

I already have a calendar table which lists working dates/times and a function as follows:

CREATE TABLE [dbo].[iHLPWorkingHours] (

[workfromdt] [datetime] NOT NULL ,

[worktodt] [datetime] NOT NULL

Sample data:

WorkFromDt WorkToDt

02/01/2007 08:00:00 02/01/2007 18:00:00
03/01/2007 08:00:00 03/01/2007 18:00:00
04/01/2007 08:00:00 04/01/2007 18:00:00
05/01/2007 08:00:00 05/01/2007 18:00:00
06/01/2007 06/01/2007
07/01/2007 07/01/2007

Non-working days such as weekends and holidays have their times removed in the iHLPWorkingHours table.

To calculate the call duration I use the following function:

CREATE FUNCTION dbo.TotalCallDuration
(
@.fromdt DATETIME,
@.todt DATETIME
)
RETURNS INT

AS

BEGIN

RETURN
(
SELECT CAST((SUM(DATEDIFF(MINUTE,workfromdt,worktodt)) -
DATEDIFF(MINUTE,MIN(workfromdt),@.fromdt) -
DATEDIFF(MINUTE,@.todt,MAX(worktodt))) AS DECIMAL(9,2))
AS working_hours
FROM ihlpWorkingHours
WHERE NOT (@.fromdt >= worktodt OR @.todt <= workfromdt)
HAVING MIN(workfromdt) <= @.fromdt AND MAX(worktodt) >= @.todt
)
END

Then pass in the opening and closing dates from the Helpdesk Call to the function to calculate the duration in minutes:

select callid, openeddatetime,
closeddatetime, dbo.TotalCallDuration(openeddatetime, closeddatetime) as duration
from ihlpcall
statusid='closed'

This works fine when a call has been opened or closed within working hours M-F but for calls that have been opened or closed outside of these times (after 18:00 and before 08:00) M-F or Weekends the function returns a null value for the call duration.

Is there any way the function can be altered to compensate for calls opened or closed outside of working hours.

Thanks in advance.

Paul

I think this will give you the logic you need. The nested query builds a set of all to-from ranges valid for the query. It corrects the from and to dates when a full date hasn't expired. It then calculates the minutes in the ranges and sums them.

There are some more elegant solutions to this, but I think this is probably the most readible.

Please note, if a call is opened and closed outside working hours without spanning a working period, this will still return NULL.

Code Snippet

select sum(datediff(minute, x.fromdt, x.todt))
from (
select
case
when @.fromdt >= workingfromdt then @.fromdt
else workingfromdt
end as fromdt,
case
when @.todt <= workingtodt then @.todt
else workingtodt
end as todt
from ihlpworkinghours
where workingtodt >= @.fromdt AND
workingfromdt <= @.todt
) x

|||

Hi Brian,

many thanks for your response, I have tried pasting the code snippet into my function but it fails the sysntax check, with the following error:

Error 1075: RETURN statements in scalar valued functions must include an argument

am I missing something?

Thanks

Paul

|||

If you are using the code in a function, the function must return some value. Declare a variable of an appropriate type, assign the results of the SELECT statement to that variable, and then return the variable.

To keep things simple, I recommend just testing the results as a simple SELECT statement, verify it's accurate, and then work on migrating it to a function.

Good luck,
Bryan