Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Count days with date difference

IDActualDateChangingLocationCheckoutDateDays
10014/30/2017Room1015/7/20171

1001

4/30/2017Room1115/7/20171
10015/1/2017Room1235/7/20173
10015/4/2017Room1415/7/20173

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.

1 Solution

Accepted Solutions
sunny_talwar

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

Capture.PNG

View solution in original post

9 Replies
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
Not applicable
Author

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
Not applicable
Author

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

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

Capture.PNG

Anonymous
Not applicable
Author

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
Not applicable
Author

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
Not applicable
Author

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.