Calculating the completion ratio

Use case

You need to calculate the completion ratio based on the start, end dates of project phases.

Solution

1. Switch the page to the edit mode.

2. Insert the Table Transformer macro and paste the table 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 Ratio'>100
   THEN "100%"
   WHEN 'Completion Ratio'<0
   THEN "0%"
   ELSE ROUND('Completion Ratio')+"%"
   END  AS 'Completion Ratio'
   FROM 
   (SELECT *, 
   DATEDIFF(DAY,'Start Date',"today")/
   DATEDIFF(DAY, 'Start Date', 'End Date')*100 AS 'Completion Ratio'
   FROM T1)

   SQL

6. Click Next. 

7. Save the macro and the page.