Calculated Dimension or Expression or Script to show previous date to current date

[#1]

We have a report request to give the difference in days where the report will show how many days it has been since the last event.

Example:

ID     Name     ChangeDate     ChangeType     DaysSinceLastChange

99    John     01/01/2011          Name                         365

99     John     01/14/2011          Address                     364

99     John     01/01/2010          Name                        365

99     John     01/15/2010          Address                    379

99     John     01/01/2009          Name                         0                        

99     John     01/01/2009          Address                     0

In other words, I need to know when the last time the ChangeType of "Name" changed before the current record I am on.

I am not sure how to approach this in the most effective way, and am open to suggestions.

[Gysbert_Wassenaar]

max({<ChangeType={'Name'}>} ChangeDate) will get you the last change. If you want the change before that try max({<ChangeType={'Name'}>} ChangeDate,2)

[#3]

Thank you Gysbert,

I think I am doing something wrong because I keep getting "// Error in calculated dimension";  here is exactly what I typed:

=max({<ReviewHistory.ChangeTypeID={'EMAIL'}>}ReviewHistory.ChangeDate)

I also tried

=max({<[ReviewHistory.ChangeTypeID]={'EMAIL'}>}[ReviewHistory.ChangeDate])

What am I missing?

The actual fields are:

ReviewID  :  (bigint)

ReviewHistory.ChangeTypeID : (varchar(5))

ReviewHistory.ChangeDate : (datetime)

Ex. values:

ReviewID :  58432

ReviewHistory.ChangeTypeID : EMAIL

ReviewHistory.ChangeDate : 2011-09-24 00:00:00.000

[Gysbert_Wassenaar · Accepted solution]

The problem is you're trying to use it as a calculated dimension instead of as an expression. I assumed you wanted to know the last (or before last) date of a name change. But is what you're really trying to do is calculate DaysSinceLastChange? That's probably easier calculated in the script. Something like this:

Temp:

Load

ReviewID,

ReviewHistory.ChangeTypeID as ChType,

ReviewHistory.ChangeDate as ChDate

from ...mysource...;

Result:

Load

ReviewID,

ChType,

ChDate,

if(ReviewID&ChType=previous(ReviewID&ChType),interval(ChDate-previous(ChDate))) as DaysSinceLastChange

resident Temp order by ReviewID,ChType,ChDate;

Drop Table Temp;

I hope all the parentheses match....

[#5]

Thank you very much; the script works.  However, it is returning a date/time value not a count of days.  When I use Num() I get a long decimal number; when I use Num#() I get a hour value.  When I use 'dd' in the interval step it returns 0, even when it has been several days.  I am going to have to do some further research; but I think it may have to do with using a DENSE_RANK() type of command on the SQL side to produce a better view to report off of.

If I don't get it figured out by tomorrow; I'll mark the question as answered as you did give me several options to try.

[Gysbert_Wassenaar]

The interval should work, but if you try floor(ChDate-previous(ChDate)) or floor(ChDate)-floor(previous(ChDate))

[#7]

Thank you for your help Gysbert.  I am taking another route because I couldn't get this to work properly; not from your instruction, but from the dataset.  There are some hidden factors that I need to consider, otherwise I get recursive joins and bad data.

However, I will be using your techniques for other things in the future.

[#8]

For anyone who was curios; I am going to be using a view based on the results from the following query; which is where I got a dataset to model after:

DROP TABLE #_Temp1

;WITH Changes_CTE AS

    (

    SELECT ReviewID, [ReviewHistory.ChangeTypeID], [ReviewHistory.ChangeDate],

        ROW_NUMBER() OVER (PARTITION BY ReviewID,[ReviewHistory.ChangeTypeID] ORDER BY [ReviewHistory.ChangeDate]) AS SEQUENCE

    FROM {ReviewHistoryView}

    )

SELECT b1.ReviewID, b1.[ReviewHistory.ChangeTypeID], b1.[ReviewHistory.ChangeDate],b1.SEQUENCE

INTO #_Temp1

FROM Changes_CTE b1

LEFT OUTER JOIN Changes_CTE b2 ON b1.ReviewID = b2.ReviewID

            AND b1.Sequence = b2.Sequence + 1

WHERE (b1.ReviewID <> b2.ReviewID

    OR b1.[ReviewHistory.ChangeTypeID] <> b2.[ReviewHistory.ChangeTypeID]

    OR b2.[ReviewHistory.ChangeTypeID] IS NULL)

GROUP BY

b1.ReviewID, b1.[ReviewHistory.ChangeTypeID], b1.[ReviewHistory.ChangeDate],b1.SEQUENCE

ORDER BY b1.[ReviewHistory.ChangeDate] DESC

;

SELECT t1.ReviewID,T1.[ReviewHistory.ChangeTypeID],

T2.[ReviewHistory.ChangeDate] as PriorChangeDate,

DATEDIFF(dd,T1.[ReviewHistory.ChangeDate],T2.[ReviewHistory.ChangeDate]) as DayDiffFromPriorChange

FROM #_Temp1 T1

INNER JOIN (SELECT * FROM #_Temp1) T2

ON

T1.ReviewID = T2.ReviewID

AND T1.[ReviewHistory.ChangeTypeID] = T2.[ReviewHistory.ChangeTypeID]

WHERE

T2.Sequence = T1.Sequence - 1

ORDER BY

T1.[ReviewHistory.ChangeTypeID],T1.[ReviewHistory.ChangeDate] desc