How to build a card which show the latest-date data only?

julienj

Hello,
I have lots of datasets, which are updated by daily/weekly/bi-weekly. They dont have a specified update date.
I'd like to build cards based on them which show the "latest-date" data only, and should be updated automaticly when datasets updated.
I notice "date_range" in domo cards build don't have a non-specified latest_date field.
And if using a beastmode to be added as a filter, it will report error.
CASE
WHEN `aggregation_date` = max(`aggregation_date`) then 'Yes'
ELSE 'No'
END as latest_date

 

Do you have a workaround solution? 

Thanks.
-Julien

Godzilla

What we've done before is add a last day, last week or last month indicator column and populated the column with an X for those records. Then in Domo, you just add a filter to the cards so it only shows where the Last Day/Week/Month column is set to X. 

 

The donwside of doing it this way is that the time grain at the top becomes useless. 

guitarhero23

Not sure I understand the requirements so my comment might not be so helpful  but have you looked at using the _Batch_Last_Run_ field that displays when your DataSet was last run/updated?

happyzoom

Thanks TravisSimpson.

I want my domo cards be automically updated with latest data only. 

As you see attached pics, the "_Batch_Last_Run_ field" is an specified date.  I must specify an exact date as the filter in the cards building.  So in this way, the card won't be automically updated later.

If the "_Batch_Last_Run " is a boolean field, then it will be helpful in this case. I could set "_Batch_Last_Run " =true as the filter directly.

We have lots of similar usage on domo cards, please help. Thanks.

 

 

Unknown

Does anyone have an answer to @happyzoom's and @julienj's questions?

Godzilla

What we've done before is add a last day, last week or last month indicator column and populated the column with an X for those records. Then in Domo, you just add a filter to the cards so it only shows where the Last Day/Week/Month column is set to X. 

 

The donwside of doing it this way is that the time grain at the top becomes useless. 

julienj

Thanks Godzilla and domo all. I'd like to have a try on this.

annelieu

I actually found a solution for this using ETL instead of SQL. I did a Rank and Window function on the Batch ID field, where I ranked the ID by descending order so that the latest batch will also have a rank of 1. Then I did a filter in the ETL to only show rows with a rank of 1 (you can do this filter with Beast Mode instead if necessary).