topic Re: Intervals between CretaedDate and CreatedEnd in QlikView

Intervals between CretaedDate and CreatedEnd

sreeni_qvd — Wed, 25 Nov 2020 16:16:04 GMT

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:

Re: Intervals between CretaedDate and CreatedEnd

sunny_talwar — Sun, 22 May 2016 06:14:21 GMT

This?

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;

Re: Intervals between CretaedDate and CreatedEnd

sreeni_qvd — Sun, 22 May 2016 07:05:04 GMT

Yes this is the correct.

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

Re: Intervals between CretaedDate and CreatedEnd

swuehl — Sun, 22 May 2016 10:03:02 GMT

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

Re: Intervals between CretaedDate and CreatedEnd

sunny_talwar — Sun, 22 May 2016 13:49:56 GMT

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

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

Re: Intervals between CretaedDate and CreatedEnd

swuehl — Sun, 22 May 2016 16:21:17 GMT

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.

Re: Intervals between CretaedDate and CreatedEnd

sunny_talwar — Sun, 22 May 2016 16:27:07 GMT

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

Re: Intervals between CretaedDate and CreatedEnd

swuehl — Sun, 22 May 2016 16:29:45 GMT

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

Nothing really wrong with your solution so far.

Re: Intervals between CretaedDate and CreatedEnd

sunny_talwar — Sun, 22 May 2016 16:52:43 GMT

My bad