Use case

You have a table with two columns: Target date and Completion date. You need to compare and rate these dates:

  • If the completion date is within 5 days, the status is .
  • If the completion date is more than 5 days late, but less than 10 days, the status is .
  • If the completion date is more than 10 days, the status is .

Solution

  1. Switch the page to the edit mode.
  2. Insert the Table Transformer macro and paste the tables or the macros outputting tables within the macro body.
  3. Select the macro and click Edit.
  4. In the Presets tab select Custom transformation and click Next.

  5. Enter the following SQL query:

    SELECT *,
   CASE
      WHEN
         (('Completion date' - 'Target date') / "24h") < 5 
      THEN "ON TIME" 
      WHEN
         (('Completion date' - 'Target date') / "24h") > 5 
         AND 
         (('Completion date' - 'Target date') / "24h") <= 10 
      THEN "LATE" 
      ELSE "VERY LATE" 
   END
   AS 'Rating' 
FROM T1
  6. Click Next.
  7. Define the date format .
  8. Save the macro and the page.

You can use FORMATWIKI function to insert statuses into a table.