Intervals between CretaedDate and CreatedEnd

[sreeni_qvd]

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:

     

[sunny_talwar]

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;

[sreeni_qvd]

Yes this is the correct.

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

[swuehl]

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.

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]

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]

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