With the way you have that calculation set up, I don't think you'd really need to change it to work in the load script. You could make a few tweaks for concision, though:
[New]:
Load
(
Sum( If([Value Type] = 'Actual', [Total Order Value £]) )
-
(
Sum(
If([Site] = '1' And [Value Type] = 'Actual' And [Total Order Value £] > 0, [Total Costs £]
, If([Site] = '2'And [Value Type] = 'Actual' And [Test Group] <> 'Other', ([Total Order Value £] * 0.025) + [Total Costs £]
, If([Site] = '3'And [Value Type] = 'Actual' And Match([Test Group], 'T 1', 'T 2', 'T 3', 'T 4') > 0, ([Total Order Value £] * 0.065) + [Total Costs £]
, If([Site] = '1' And [Test Group] = 'Other' And WildMatch([Product Description], '*Transport*', '*Emballage*') > 0, ([Total Costs v3] / [Curr Rate])
, If([Value Type] = 'Actual' And [Total Order Value £] > 0, [Total Costs £]) ))))
)
+
Sum( If([Value Type] = 'Actual', [Total Order Value £] * [Discount %]) )
+
(
Sum( If([Value Type] = 'Actual', [Total Order Value £] * [Rebate %]) )
+
Sum( If([Site] = '3' And [Value Type] = 'Actual' And [Customer Name] = 'ABC' And Match([Test Group], 'T 1', 'T 2', 'T 3', 'T 4') > 0, [Total Order Value £] * 0.03) )
)
+
Sum( If([Site] = '3' And [Value Type] = 'Actual' And [Test Group] = 'Balustrades', [Total Order Value £] * 0.05) )
+
Sum( If([Product Description] Like '*VI*' And [Value Type] = 'Actual', [m2] * [£/m2 Counterpane]) )
+
Sum( If([Value Type] = 'Actual', [SP Perim]) )
*
Sum( If([Value Type] = 'Actual', [£/m2 Spacer]) )
+
Sum( If([Product Description] Like '*VI*' And [Value Type] = 'Actual' And [Site] = '3', [Order Amount] * 17.50) )
)
)
/
Sum( If([Value Type] = 'Actual', [Total Order Value £]) ) as [Script Calc]
Resident [Data];
That script works assuming that all of those fields are available from a single table that we are doing a Resident load from, the [Data] table in this case.
Beyond just formatting, one of the updates I made to your script was that instead of using separate If() statements for each [Test Group] or [Product Description] values, you can test for all necessary values using the Match() function or WildMatch() function. These functions take a list of string arguments and test to see if they match any of the values in the provided field. They return the index of the first argument that matches. So this function:
Match([Test Group], 'T 1', 'T 2', 'T 3', 'T 4') > 0
Tests to see if [Test Group] matches either 'T 1', 'T 2', 'T 3', or 'T 4' in the current record. If the current record is 'T 2', then the function returns 2 because that was the second option in the list. If the current record is 'T 3', then the function returns 3. If, however, the current record doesn't match any of those values, the function returns 0. So basically the Match() function is acting like an in keyword in SQL. The Match() function is case-sensitive, whereas the WildMatch() function is not case-sensitive. The WildMatch() function can also match on substrings using the question mark ? or asterisk * operators.
Also, if you wanted to keep your expression in the frontend in any way, I would consider using Set Analysis where you can, like this:
(
Sum({<[Value Type] = {'Actual'}>} [Total Order Value £])
-
(
Sum(
if([Site] = '1' and [Value Type] = 'Actual' and [Total Order Value £] > 0, [Total Costs £]
, if([Site] = '2'and [Value Type] = 'Actual' and [Test Group] <> 'Other', ([Total Order Value £] * 0.025) + [Total Costs £]
, if([Site] = '3'and [Value Type] = 'Actual' and Match([Test Group], 'T 1', 'T 2', 'T 3', 'T 4') > 0, ([Total Order Value £] * 0.065) + [Total Costs £]
, if([Site] = '1' and [Test Group] = 'Other' and WildMatch([Product Description], '*Transport*', '*Emballage*') > 0, ([Total Costs v3] / [Curr Rate])
, if([Value Type] = 'Actual' and [Total Order Value £] > 0, [Total Costs £]) ))))
)
+
Sum({<[Value Type] = {'Actual'}>} [Total Order Value £] * [Discount %])
+
(
Sum({<[Value Type] = {'Actual'}>} [Total Order Value £] * [Rebate %])
+
Sum({<
[Site] = {'3'}
, [Value Type] = {'Actual'}
, [Customer Name] = {'ABC'}
, [Test Group] = {'T 1', 'T 2', 'T 3', 'T 4'}
>}
[Total Order Value £] * 0.03 )
)
+
Sum({<
[Site] = {'3'}
, [Value Type] = {'Actual'}
, [Test Group] = {'Balustrades'}
>}
[Total Order Value £] * 0.05 )
+
Sum({<
[Product Description] = {"*VI*"}
, [Value Type] = {'Actual'}
>}
[m2] * [£/m2 Counterpane] )
+
Sum({<[Value Type] = {'Actual'}>} [SP Perim])
*
Sum({<[Value Type] = {'Actual'}>} [£/m2 Spacer])
+
Sum({<
[Product Description] = {"*VI*"}
, [Value Type] = {'Actual'}
, [Site] = {'3'}
>}
[Order Amount] * 17.50 )
)
)
/
Sum({<[Value Type] = {'Actual'}>} [Total Order Value £] )
