Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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.

1 Solution

Accepted Solutions

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

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


talk is cheap, supply exceeds demand
7 Replies

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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

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


talk is cheap, supply exceeds demand
Not applicable

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

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.

Not applicable

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

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

Community Browser