Days - Date Difference

[teccum]

I need to create a time difference column in my report. Actually, I have to create an attribute called 'Out Of Compliance'. The formula for this is

(Claim Received Date - Claim Paid Date) > 60.

I have created the derived attribute for Claim Received Date and Claim Paid Date by giving the expression.

Year(([DAY_SHORT_NAME] - Year([DAY_SHORT_NAME]))).

But I am not able to create attribute difference and I get error message when I enter the number 60 and validate it.

One of the points is the claim received date and claim paid date are attribute roles and not actual attributes and hence when I use the Applysimple function, I am not able to choose them as columns since it is not available in the database. And, since these date attributes are created from Date field of Day Dimension Table, I could not either create a metric.

Any idea on how to accomplish this.

Thanks
Teccum.

 

[nlim]

if claim_received and claim_paid are in the same fact table, then you can try the following:

create attribute called days_to_payment, point to single table with 2 columns, define as claim_paid_column-claim_received_column.

You might need to do some database specific applysimple to get the number in days.

I would avoid using the roles or aliases.

If the 2 dates are not in the same table, you should define a logical view in MSTR v8 that creates such a single table, and then create the attribute as above.

 

[teccum]

nlim,

Thanks for the response.

Both these attributes are NOT in the fact table. Claim Paid Date and Claim Received date were created as Role Attributes by creating two alias tables from the DW_DAY dimension table and defined both these attributes by linking to the Day_id in the alias table.

CLAIM_FACT table consists of the Claim_received_date.day_id and Claim_paid_date.day_id. These are the roles of Day_id from the DW_DAY dimension table.

The database structure is as below.

Claim_Fact: (Fact Table)

Claim_id
Claim_received_date.day_id
claim_paid_date.day_id
claim_service_start_date
claim_service_end_date
claim_no
Provider_id
Specialty_id...etc.

DW_DAY: (Dimension Table)

day_id
day_date
day_of_week
day_flag....etc.

Day_id has four different roles(linked two times for four different date roles) with the claim_fact. Hence I have created four different attribute roles.

I tried creating the same logic by using Custom Groups. I included the "Out of Compliance" attribute which I created by giving the expression

Year(([DAY_SHORT_NAME] - Year([DAY_SHORT_NAME]))). and selected the option "greater than or equal to" from the drop down list and defined it.

But there is a requirement in my report where I need to do a count of (out of compliance). I now could not create a metric since I could not do a count of a custom group.

I also tried using Applysimple. My database is SQL Server 2000 and I defined the expression as given below but does not validate and give "expression is invalid"

ApplySimple(("Datediff (YY, #0,#1)", [Claim Received Date],[Claim Paid Date])> 60)

Any other ideas and where I am going wrong.

Thanks Much.
Teccum.

 

[teccum]

nlim,

I believe, I have temporarily resolved this. These are the steps which I have performed to take care of calculating the difference in days between two dates attribute(attribute roles).

1. Defined an attribute called 'Non-Compliant' by
using the expression below.

Year(([DAY_SHORT_NAME] - Year([DAY_SHORT_NAME]))).

2. Created a attribute qualification filter called
'non-compliant filter' and set the condition for
the attribute 'Non-Compliant' greater than 60.

3. Created a metric called 'non-compliant' by
doing a count of derived attribute
'Non-Compliant' and included the condition
'non-compliant'.


I guess this should work. If not I will post it
onceagain. I plan to create another metric for
compliant with a filter condition less than 60.

The statement which I put in my first post which is

"since these date attributes are created from Date
field of Day Dimension Table, I could not either
create a metric."

is wrong since I am able to see derived attribute
'Non-compliant' in the Object Browser.

Thanks
Teccum.