Accounting for the monthly budget

Use case

You need to calculate the difference between monthly budget and actual expenses by categories and display the appropriate status: "Under budget", "Over budget", "On budget".

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 *,
   (T1.'Actual'- T1.'Budget') AS 'Difference, $',
   (T1.'Actual' / T1.'Budget' * 100) AS '% Budget',
   CASE
   WHEN (T1.'Actual' / T1.'Budget' * 100) < 100
   THEN FORMATWIKI("{status:colour=Green|title=Under budget}")
   WHEN (T1.'Actual' / T1.'Budget' * 100) > 100
   THEN FORMATWIKI("{status:colour=Red|title=Over budget}")
   ELSE FORMATWIKI("{status:colour=Yellow|title=On budget}")
   END AS 'Indicator'
   FROM T*

   SQL

6. Click Next. 

7. Save the macro and the page.