Filtering Dates to Get the Last Access Date

Reply
White Belt

Filtering Dates to Get the Last Access Date

Hello All,

 

I have created a card with the following fields (see Table 1) and trying to edit it using the Beast mode. I am basically looking for help to filter rows the courses last access by a user with the number of days (Today’s Date - DayAccessed). Below is a user “John Doe” who accessed two courses on three different days.

 

Table 1

FirstName

LastName

CourseName

DayAccessed

John

Doe

Fall 2020 CIS 101

2020-11-21 00:00:0000

John

Doe

Fall 2020 CIS 101

2020-11-20 00:00:0000

John

Doe

Fall 2020 CIS 101

2020-11-19 00:00:0000

John

Doe

Fall 2020 BIO 222

2020-11-19 00:00:0000

John

Doe

Fall 2020 BIO 222

2020-11-17 00:00:0000

John

Doe

Fall 2020 BIO 222

2020-11-16 00:00:0000

 

 

Result will look like this table 2 (below) showing:

  1. Only the row that includes the last day the user accessed each course
  2. A new additional column (DaysAccessed) containing the number of days since last accessed
  3. Plus change the DayAccessed field from “2020-11-21 00:00:0000” to “2020-11-21”

Table 2

FirstName

LastName

CourseName

DayAccessed

DaysAccessed

John

Doe

Fall 2020 CIS 101

2020-11-21

4

John

Doe

Fall 2020 BIO 222

2020-11-19

6

 

Thanks in advance.

Syed

 


Accepted Solutions
Black Belt

Here's a screenshot of what it would look like in analyzer to backup my earlier post.

classes.PNG

 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post


All Replies
Yellow Belt

Hey Syed,

 

RE: DateDiff Column -- Add a calculated field for DATEDIFF(CURRENT_DATE - `DayAccessed`) - it will give you the number of days between "today" and the value in the `DayAccessed` column.  You can then add this calculated field to your table.

 

RE: Date Formatting -- I'd personally recommend handling this at the ETL level so you don't have to constantly carry it around in your BeastModes. You would do this via CAST/CONVERT/SET COLUMN TYPE depending on your ETL vehicle. I'm making this recommendation presuming your "timestamp" values are meaningless. If you must handle it at a BeastMode level, then you can use DATE or DATE_FORMAT to convert to your desired output. 

Black Belt

You can show the most recent day accessed by clicking on the pencil icon for the Days Acccessed column in the column list and choose MAX. You can also format the date to just show the date in the same edit area by clicking on Format and choose Date Shorthand. 

To get the difference between the first day accessed and the last day accessed by a person and class, create a beast mode that contains this formula:

DATEDIFF(MAX(`DayAccessed`),MIN(`DayAccessed`))

 

You can then drag this field into your column list and it should produce the results you are looking for. 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Black Belt

Hey @CCCDomo 

 

Within an ETL you'd need to do a Group By tile to group based on the First, Last and Course names. Then for your resulting column select the Maximum aggregate. That will get you the last time it was accessed. You can then take that data output and pipe that into a Date Operations tile to calculate the number of days since last accessed. Just select "Difference Between Dates" for the operation and "Days" for your unit of measurement. Then choose "Value" and then select "current date" from the date selector for the first value and your "DayAccessed" for the second value. This will then get you the data you need.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
White Belt

Thanks for your super quick reply RACERX!

 

I am getting the following error when I try to add the filed using DATEDIFF(CURRENT_DATE - `DayAccessed`) although the Best mode validate without any problem.

 

Black Belt

Here's a screenshot of what it would look like in analyzer to backup my earlier post.

classes.PNG

 




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**

View solution in original post

Black Belt

@MarkSnodgrass 's solution would work as well and likely be simpler. I have seen DATEDIFF not work correctly sometimes so I prefer the UNIX_TIMESTAMP method where it converts dates to a number in seconds and then doing some simple math:

 

ROUND((
  UNIX_TIMESTAMP(CURRENT_DATE) - UNIX_TIMESTAMP(MAX(`DaysAccessed`))
/
  60 * 60 * 24)
- 0.5, 0)

 

Using -0.5 and the ROUND function it's truncating the number of days to be whole numbers that have passed.



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
White Belt

Thanks MarkSnodgrass!

 

I go the first two working including the last date of access filtered and the date format changed.

To get the number of days last access I made minor changes to the code you provided as below and it is working too.

DATEDIFF(CURRENT_DATE(),MAX(`DayAccessed`))

 

Announcements
New & Improved Dojo: Get excited for a new feel and additional features in the online community.

Site will be down February 4-5th for maintenance.

Launch coming February 8th!