Replace Expression code with EditScript code

[#1]

I am using following in the expression which works just fine(shown in red). For some other reasons(cant elaborate here) i want to replace below expression code(in red) with EditScript code(in green)

Expression Code :-

=sum({<Market={'Italy'}>}[Unique Leads 1 Month])/sum({<Market={'Italy'}>}[Active Contacts])

Edit Script Code:-

sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts])) as LeadsByActiveContacts 

I tried to do the following to replace the expression but it doesn't work!

=LeadsByActiveContacts

[richnorris]

Yeah, you do that like I said above. You store the code into a variable in edit script, and then in your code, you use the dollar expansion of that variable. It's actually quite good practice, as it means you can reuse the calculation in different expressions and tables without having to re type it out each time.

[#3]

This is what i tried to do as you mentioned. See the text in Bold below.This gives an error though when i load script.As the variables which are used in this statement are not yet loaded inot memory??

SET ThousandSep=',';

SET DecimalSep='.';

SET MoneyThousandSep=',';

SET MoneyDecimalSep='.';

SET MoneyFormat='$#,##0.00;($#,##0.00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

LET LeadsByActiveContacts = sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts]));

LOAD date(Month,'MMM-YY') as Month,

     COD_MARKET as Market,

     Brand_Desc,

     [Source Channel],

     [Active Contacts],

     [Unique Leads last 6 Months],

     [Unique Leads 1 Month],

    [Unique Permissions],

     Total_Permission,

     Contacts

FROM

(ooxml, embedded labels, table is Sheet2)

[Kushal_Chawda]

=only(LeadsByActiveContacts)

[#5]

Already tried this too ! doesn't work. It will display blanks

[miguelbraga]

Call the Let function after the Table load, It's better practice

[Kushal_Chawda]

can you share sample application?

[#8]

If i try to place it after the table load it doesn't accept the "LET" function. I tried that too.

Can you modify the above code and show me what you mean?

[settu_periasamy]

Hi,

Try this

In script - use 2 single quotes

LET LeadsByActiveContacts = 'sum(if(COD_MARKET=''Italy'',[Unique Leads 1 Month]))/sum(if(COD_MARKET=''Italy'',[Active Contacts]))';

Expression

$(LeadsByActiveContacts)