Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
sreeni_qvd
Creator
Creator

Intervals between CretaedDate and CreatedEnd

Hi All,

I would like to do the lag calculation for Historical data in Qlikview.I would like to have a new column (new expression?) with the count differences between current day and the days before for every case Status 4/15/2013  12:42:23 PM - 4/17/2013  9:17:12 AM.

Please find the attachment of excel file.

Expected output:

      Capture.PNG

8 Replies
sunny_talwar

This?

Capture.PNG

Table:

LOAD SERIAL_NUM,

     CASE_STATUS_CD,

     CREATED

FROM

[SampleData (2).xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *,

  If(SERIAL_NUM = Previous(SERIAL_NUM), Previous(CREATED), CREATED) as CREATED_NEW

Resident Table

Order By SERIAL_NUM, CREATED desc;

DROP Table Table;

sreeni_qvd
Creator
Creator
Author

Yes this is the correct.

But what is the expression did you used for total count (566:25:25 PM)

swuehl
MVP
MVP

Sunny is using an expression

=Interval(CREATED_NEW - CREATED)

and a total mode 'sum of rows'.

Since in general we should use aggregation functions in expressions, you could also use the default total mode 'expression total' and as expression

=Interval(Sum( CREATED_NEW - CREATED))

Use Aggregation Functions!

Besides this, you may need to decide how you want to handle the records with the last timestamp per SERIAL_NUM.

With the code suggested by Sunny, a duration of 0 will be assigned, while you may want to calculate the duration until Today() (or another max timestamp) or another value based on a logic involving the CASE_STATUS:

Table:

Directory;

LOAD SERIAL_NUM,

    CASE_STATUS_CD,

    CREATED

FROM

[SampleData.xlsx]

(ooxml, embedded labels, table is Sheet1);

FinalTable:

LOAD *, Interval(CREATED_END - CREATED) as DURATION;

LOAD *,

  If(SERIAL_NUM = Previous(SERIAL_NUM), Previous(CREATED), TODAY()) as CREATED_END

Resident Table

Order By SERIAL_NUM, CREATED desc;

DROP Table Table;

It's also explained in detail in this blog and the linked tech doc:

Creating a Date Interval from a Single Date

sunny_talwar

So as Stefan has already mentioned that I used the Total Mode feature within straight table to get the sum of rows.

Capture.PNG

Pivot table options are:

Stefan's provided -> =Interval(Sum( CREATED_NEW - CREATED))

or may be based on Aggr() using some combination of dimension if the chart is more complex then the one we have proposed above.

Finally, the script can be tweaked to default to any date (not just today) for the last timestamp. But based on the image you posted, I would assume that you did want to default it to the same time so that you see a 0. Let us know if that is not the case and we can always offer another solution (if Stefan's or my solution doesn't work for you)

Best,

Sunny

swuehl
MVP
MVP

Could you explain the logic of the calculated columns in your screenshot sample?

It looks like you use a different logic per Case status, since all records belong to the same SERIAL_NUM, but CREATED_NEW is not always capturing the following timestamp.

sunny_talwar

Hey Stefan -


I am not next to my computer right now. So won't really be able to provide any details. But since you were willing to respond, I am sure something is def. wrong with what I have posted. I will def. look at my screenshot and get back to you


Best,

Sunny

swuehl
MVP
MVP

Sunny, I was actually addressing the OP to clarify what he requested / shows in the screenshot.

Nothing really wrong with your solution so far.

sunny_talwar

My bad