Table Transformer Macro - custom SQL

[Megan]

I am trying to calculate two values using the Table Transformer Macro and custom SQL.

The values are Cost of Delay and WSJF. The calculated sum of Cost of Delay is used to calculate WSJF.

Here is a sample table I am utilizing:

Variable	Score Value
Revenue	8
Cost Savings	1
Development Effort	13
Strategic Value	20
Legal Risk	5
Time Criticality	5

I was able to get the two values calculated and displayed separately, and displayed next to one another, but ideally I'd like them to be part of the same table.

To calculate Cost of Delay I used the following function

SELECT SUM ('Score Value') As 'Cost of Delay'
FROM T1
WHERE Variable IN ("Revenue", "Cost Savings", "Strategic Value", "Time Criticality");

On it's own it prints out as expected

Cost of Delay	34

To calculate the other value I need, WSJF, I used the following

`SELECT 100* 
(
    (
SELECT SUM ('Score Value') As 'Cost of Delay'
FROM T*
WHERE Variable IN ("Revenue", "Cost Savings", "Strategic Value", "Time Criticality")
) / (
SELECT SUM ('Score Value') As 'Other'
FROM T1
WHERE Variable IN ("Legal Risk", "Development Effort")
  ) 
 )
 AS WSJF;`

Which correctly gives me

WSJF	189

Ideally I'd like a table be displayed with the combination of these two

VALUE	SCORE
Cost of Delay	34
WSJF	189

I am at a loss for how to connect these two functions into one. I have tried asking stack overflow but every suggestion leads to errors, I'm guessing due to nuances with the macro and confluence.

[Katerina Rudkovskaya _Stiltsoft_]

Hi @Megan,

I can suggest the following workaround for the case:

The internal Table Transformer macros are used for your original queries and ad additional line with future headers:

SELECT
"Score" AS 'Value',
SUM ('Score Value') AS 'Cost of Delay'
FROM T1
WHERE Variable IN ("Revenue", "Cost Savings", "Strategic Value", "Time Criticality")



SELECT
"Score" AS 'Value',
100*
(
(
SELECT SUM ('Score Value') As 'Cost of Delay'
FROM T*
WHERE Variable IN ("Revenue", "Cost Savings", "Strategic Value", "Time Criticality")
) / (
SELECT SUM ('Score Value') As 'Other'
FROM T1
WHERE Variable IN ("Legal Risk", "Development Effort")
)
)
AS WSJF



And we also use the Settings -> Transpose result table option to transpose tables for both Transformers.

The external Table Transformer macro is used simply to merge your result tables:

SELECT * FROM T*



Hope it helps.

[Megan]

Hello - do you know how it is possible to do this calculation if I need to add weights to each of these values? So calculated the weighted sum 

[Stiltsoft support]

You may switch to the Table Spreadsheet macro and perform your calculations in a more user-friendly Excel-like style.

[Megan]

Do you have a calculation if I don't have that macro available to me?

[Dominic Lagger]

Hi @Megan 

My first thought was to use the union statement like in this example

Here is the UNION documented. 

Hope this helps.

Regards, Dominic