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

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

Best Answer

  • Godzilla
    Godzilla 🟢
    Accepted Answer

    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. 

Answers

  • 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?

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

     

     

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

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

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

This discussion has been closed.