CT has internal tools for checking data freshness, e.g. the OOTB reports in the Data Quality section and also some tools within TBM Studio. However with some clients we found the need for a custom solution where we only trace a certain scope of tables with custom rules, so we are not confusing with full list of tables + be able to focus on our own freshness rules. 

In this article I will explain how to use editable table to dynamically monitor the upload status with traffic lights based on custom rules.

We will be able to

• decide which tables are checked in this report (which also means, we have to enter them, so new tables have to be added)
• decide the rules for flagging red, green (and if needed, yellow)
• put this into report surface, potentially visible to all users

We define following validity rules:

Steps:

1. Create "Editable Table" with e.g. four label columns:
   • Dataset Name
   • Category
   • Upload Date Criteria
     In here we define Possible Values:
     Upload Date during this year,Upload Date after this month,Upload Date during this month,Upload into this month,No Check
   • Comment
     
     
     
     
     
2. Create the new report and insert the Editable Table component with those four columns
3. Create the following formula columns into the report table via Data -> Insert Formula Column:
   
   

   Formula column Name	Formula
   Upload Date	=DateFormat(TableInfo("Last Updated",{Dataset Name}), "yyyy-MM-dd")
   File Name	=TableInfo("Source",{Dataset Name})
   Month Current	=Months(CurrentDate())
   Month Upload	=Split(Months(Upload Date),1,".")
   Month Mininum for Green for Upload Date	=If({Upload Date Criteria}="Upload Date after this month",Month Current+1,If({Upload Date Criteria}="Upload Date during this month",Month Current,If({Upload Date Criteria}="Upload Date during this year",Month Current-CurrentDate("M")+1,0)))
   Uses Data from This Period	=TableInfo("Updated This Period",Dataset Name)
   Traffic Light Color	=If({Upload Date Criteria}="No Check" OR {Upload Date Criteria}="","No Check",If((Upload Date Criteria="Upload into this month" AND Uses Data from This Period="true") OR (Upload Date Criteria!="Upload into this month" AND Month Upload>=Month Mininum for Green for Upload Date),"green","red"))
   Upload Status	="<div align='center'>"&Icon("3colorcircles",Traffic Light Color="green",Traffic Light Color="yellow",Traffic Light Color="red")

4. Fill the table with some table names, categories, and upload rules. Validate its working.
5. Hide the columns marked in yellow in following printscreen in the report table.