topic Re: Calculated Dimension or Expression or Script to show previous date to current date in QlikView

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

Thu, 14 Feb 2013 20:07:30 GMT

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.

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

Gysbert_Wassenaar — Thu, 14 Feb 2013 20:17:59 GMT

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

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

Thu, 14 Feb 2013 20:46:24 GMT

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

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

Gysbert_Wassenaar — Thu, 14 Feb 2013 21:01:26 GMT

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....

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

Thu, 14 Feb 2013 21:34:41 GMT

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.

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

Gysbert_Wassenaar — Fri, 15 Feb 2013 08:59:05 GMT

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

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

Fri, 15 Feb 2013 19:59:25 GMT

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.

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

Mon, 18 Feb 2013 17:50:38 GMT

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

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