topic Re: Count days with date difference in QlikView

Count days with date difference

Anonymous — Tue, 20 Jun 2017 14:31:08 GMT

ID	ActualDate	ChangingLocation	CheckoutDate	Days
1001	4/30/2017	Room101	5/7/2017	1
1001	4/30/2017	Room111	5/7/2017	1
1001	5/1/2017	Room123	5/7/2017	3
1001	5/4/2017	Room141	5/7/2017	3

I need an expression to get the Days column in Red. I need to find out Days present at each location.

Here is the logic for the expression: If ID = Next Row's ID AND If ActualDate=Next Row's Actual Date then Day=1 OR If ActualDate<Next Row's Actual Date then Days=Next Row's Actual Date - Actual Date; For last Actual Date, it is CheckoutDate - ActualDate that's why Days = 3.

I believe it will be a nested if statements with FirstSortedValue and LastSortedValue functions. Thought I will get help from any experts here. Sorry, I am not able to post my actual data here so tried to generate a similar logic.

Thank you.

Re: Count days with date difference

sunny_talwar — Tue, 20 Jun 2017 15:11:57 GMT

How did you get 1 for Room123? Shouldn't it be 3 days? 5/4/2017 - 5/1/2017?

Re: Count days with date difference

avinashelite — Tue, 20 Jun 2017 15:14:09 GMT

Whats the logic to calculate the Days ??

Re: Count days with date difference

Anonymous — Tue, 20 Jun 2017 15:18:22 GMT

Good catch Sunny. Sorry, I got messed up at work multitasking. It should be 5/4/2017 - 5/1/2017 so 3. I will update my original entry. Thank you.

Re: Count days with date difference

Anonymous — Tue, 20 Jun 2017 15:20:51 GMT

Avinash,

Thanks.

Re: Count days with date difference

sunny_talwar — Tue, 20 Jun 2017 15:26:25 GMT

Try this

=If(ActualDate = Max(TOTAL <ID> ActualDate), CheckoutDate - ActualDate,

If(ActualDate = Aggr(NODISTINCT Below(ActualDate), ID, Key), 1, Aggr(NODISTINCT Below(ActualDate), ID, Key)-ActualDate))

Re: Count days with date difference

Anonymous — Tue, 20 Jun 2017 15:40:18 GMT

I am getting a red underline under the first "Key" letter after ID. Then the result looks like below: I may also have to format the date. Going for lunch...will get back on it later to fix the date format in the SQL script.

Re: Count days with date difference

sunny_talwar — Tue, 20 Jun 2017 15:43:04 GMT

Key is a new field created in the script

Table:

LOAD *,

AutoNumber(ActualDate&ChangingLocation) as Key;

LOAD * INLINE [

ID, ActualDate, ChangingLocation, CheckoutDate

1001, 4/30/2017, Room101, 5/7/2017

1001, 4/30/2017, Room111, 5/7/2017

1001, 5/1/2017, Room123, 5/7/2017

1001, 5/4/2017, Room141, 5/7/2017

];

Re: Count days with date difference

Anonymous — Tue, 20 Jun 2017 15:52:38 GMT

Sunny! I am copying my script below and including dimensions and expressions. What do I need to modify? My actual SQL is way too complicated so I modified accordingly just for what we are working on. Please excuse any typos.

----

Days:

LOAD *,

AutoNumber(ActualDate&ChangingLocation) as Key;

SQL SELECT

ba.VisitID,

ba.ID,

CAST(ba.CheckoutDateTime as DATE) as CheckoutDate,

CAST(ba.ServiceDateTime as DATE) as KeyDate,

loc.LocationID as ChangingLocation,

loc.DateTime as ActualDateTime,

CAST(loc.DateTime as DATE) as ActualDate

FROM tb1 as ba

LEFT JOIN tb2 loc

ON ba.VisitID=loc.VisitID and loc.LocationID IS NOT NULL;

Chart Properties:

Dimensions:

1) ID

2) ActualDateTime

Expressions:

1) ActualDate

2) ChangingLocation

3) CheckoutDate

4) =If(ActualDate = Max(TOTAL <ID> ActualDate), CheckoutDate- ActualDate,

If(ActualDate = Aggr(NODISTINCT Below(ActualDate), ID, Key), 1, Aggr(NODISTINCT Below(ActualDate), ID, Key)-ActualDate))

---

I reloaded and got the below result. Still something is missing since the third result value needs to be 3 instead of "-".

Re: Count days with date difference

Anonymous — Tue, 20 Jun 2017 19:12:43 GMT

I had to add "ORDER BY loc.DateTime" at the end on the SQL script. Sunny's code then worked like a charm. Thanks Sunny.