Filtering by latest date in table

NathanDorsch

I have a dataset which appends updated content data on a daily basis (with a 1 day reporting lag). I want to have the table show the latest data, however am struggling to do so. I tried filtering by last 2 days, which works most of the day until 7 PM CT, when everything is filtered out because I guess the filter is going by UTC time.

Is there a best practice for this?

Here's the concept.... if this is the existing data, I want to only show the data corresponding to the latest data: 9/28/21.

Date (day) -- Widget -- X -- Y

9/27/21 -- Wrench -- 2.4 -- $5

9/27/21 -- Hammer -- 5.1 -- $2

9/28/21 -- Wrench -- 2.7 -- $5.20

9/28/21 -- Hammer -- 4.7 -- $2.71

GrantSmith

Hi @User_32265

You can use a beast mode to compare your date to the latest date and then filter on the result using a window function and a case statement. You can then use this and filter for Latest. If you don’t have window functions talk with your CSM as it’s a simple feature switch

CASE WHEN `date` = MAX(MAX(`date`)) OVER () THEN 'Latest' ELSE 'Older' END

GrantSmith

Hi @User_32265

You can use a beast mode to compare your date to the latest date and then filter on the result using a window function and a case statement. You can then use this and filter for Latest. If you don’t have window functions talk with your CSM as it’s a simple feature switch

CASE WHEN `date` = MAX(MAX(`date`)) OVER () THEN 'Latest' ELSE 'Older' END

NathanDorsch

Dude, you are money! Thanks!

GrantSmith

Glad I could help! If you could make sure to mark it as an accepted answer so others can easily find this I'd appreciate it!

NathanDorsch

Will do. Good to know.

NathanDorsch

@GrantSmith The "latest/older" beastmode field you provided me seems to be working... except when I apply the filter, it is acting very weird and sporadically... for example, I'll apply the filter (with the full time selected) and only the Latest option will be there. Or in other cases, the Older option will only be there. There doesn't appear to be any rhyme or reason with it... And it's definitely filtering out more rows that it shoudl when I select the Latest option...

Do you know what might be happening? Is it possible create a new field that identifies Latest or Olders in the ETL? When I attempt to copy & paste the same formula in the ETL, it doesn't permit me...

NathanDorsch

@GrantSmith I guess I'm confused on the window function. I simply had just created a beastmode calculation, but apparently need to use a window function... when I use the Rank & Window function, I get this. Is this what I'm supposed to be using? I'm not sure how to use the case statement in here....

jaeW_at_Onyx

@User_32265

if you use @GrantSmith 's window function you will only keep rows on that max() date.

example. if you had a report where every day you sent the customer table, you could ask for "the most recent version of the customer table" and guarantee that you'd see every customer.

but if your question is, "i only want to see the most recent purchase from each customer" , each customer doesn't make a sale every day. so if you filtered on max(max(date)) ... then you'd only keep customers who had a sale on that most recent day (which would exclude a ton of people.)

so if you want the most recent row for each item you can still keep using window functions OR use a rank window function in ETL.

sum(sum(1)) over (partition by item order by date desc)

this will rank each transaction per item ordered by date desc. and you could filter by rank = 1.

do a rank window function in ETL with the same over() clause i outlined.

NathanDorsch

I appreciate the feedback, but this is just driving me nuts. Here is the beastmode formula I created...

Latest date: CASE WHEN `date` = MAX(MAX(`date`)) OVER () THEN 'Latest' ELSE 'Older' END

When I put this latest date field in the table along with the date field, I notice it seems to capture everything properly. When I leave the date field in the table (and remove the latest date beastmode calculation out of the table) and then filter by latest date, it does one of several different things.... it will either only display the 'latest' option, only the 'other' option, or sometimes both.... but when I select the latest option, it doesn't seem to capture all the results that should be displayed unless I have the date included in the table. I don't understand why I need to include the date field in the table for it to properly capture everything...

@GrantSmith ??

NathanDorsch

Here's an example. You can see there are rows with the latest date equal to "latest" and "older", but in the fitler, I only get a latest option...

GrantSmith

@User_32265

That is a bit interesting as both values should be appearing in your list. Are you grouping your data by anything or are you graphing your table by month instead of by day in the date selector?

As for your ETL you can use the Group By tile with one caveat. It requires you to group by something. You can add a constant value column with a value of 1, group by that column, select the max date then join back to your original dataset on that new constant column (I typically call this column Join Column). I've attached the JSON below which you should be able to copy and paste into your Magic ETL (1.0 or 2.0). If you go this route you can then just compare your max date field and your date field to see if they're the same in a beast mode instead of using the window function. (My date field was called dt - you'll need to change it for your field)

{"contentType":"domo/dataflow-actions","data":[{"name":"Add Constants","id":"45258bf9-400b-41ab-9ae9-a90d60a3fba9","type":"Constant","gui":{"x":180,"y":180},"dependsOn":["59227c5b-3d41-4c8a-a7d0-4fb85b7dd545"],"removeByDefault":false,"notes":[],"fields":[{"name":"Join Column","type":"LONG","value":"1"}]},{"name":"Group By","id":"43b922e3-5a31-4ed5-bb02-b4a04933dd22","type":"GroupBy","gui":{"x":312,"y":228},"dependsOn":["45258bf9-400b-41ab-9ae9-a90d60a3fba9"],"removeByDefault":false,"notes":[],"addLineNumber":false,"lineNumberFieldname":null,"giveBackRow":false,"allRows":false,"groups":[{"name":"Join Column"}],"partitionedAggregation":false,"fields":[{"name":"Max Date","source":"dt","type":"MAX"}]},{"name":"Join Data","id":"63474fc7-ba57-4220-8ea7-2327e81d64fd","type":"MergeJoin","gui":{"x":420,"y":180},"dependsOn":["43b922e3-5a31-4ed5-bb02-b4a04933dd22","45258bf9-400b-41ab-9ae9-a90d60a3fba9"],"removeByDefault":false,"notes":[],"joinType":"INNER","step1":"43b922e3-5a31-4ed5-bb02-b4a04933dd22","step2":"45258bf9-400b-41ab-9ae9-a90d60a3fba9","keys1":["Join Column"],"keys2":["Join Column"],"schemaModification1":[],"schemaModification2":[],"partitioningInputId":""}]}