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
<span>SELECT</span> <span>SUM</span> (<span>'Score Value'</span>) <span>As</span> <span>'Cost of Delay'</span>
<span>FROM</span> T1
<span>WHERE</span> Variable <span>IN</span> ("Revenue", "Cost Savings", "Strategic Value", "Time Criticality");On it's own it prints out as expected
To calculate the other value I need, WSJF, I used the following
`<span>SELECT</span> <span>100</span><span>*</span>
(
(
<span>SELECT</span> <span>SUM</span> (<span>'Score Value'</span>) <span>As</span> <span>'Cost of Delay'</span>
<span>FROM</span> T<span>*</span>
<span>WHERE</span> Variable <span>IN</span> ("Revenue", "Cost Savings", "Strategic Value", "Time Criticality")
) <span>/</span> (
<span>SELECT</span> <span>SUM</span> (<span>'Score Value'</span>) <span>As</span> <span>'Other'</span>
<span>FROM</span> T1
<span>WHERE</span> Variable <span>IN</span> ("Legal Risk", "Development Effort")
)
)
<span>AS</span> WSJF;`Which correctly gives me
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.
