Count days with date difference

[Anonymous]

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.

[sunny_talwar]

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

[avinashelite]

Whats the logic to calculate the Days ??

[Anonymous]

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.

[Anonymous]

Avinash,

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.

Thanks.

[sunny_talwar · Accepted solution]

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

[Anonymous]

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.

[sunny_talwar]

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

];

[Anonymous]

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

[Anonymous]

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.