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.
Dont you just want to do:
LET LeadsByActiveContacts = sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts]))
and then where you previously had the code, do
SO, your problem is, I assume in your code you're doing something like:
sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts])) as LeadsByActiveContacts
or something to that effect. The trouble is doing it in a table load like that, is essentially running the aggregation (the summation) per row of that table. So, for every row its just going to sum that row, which will just be itself, it wont take any other rows into consideration. So for every row of your table, you'll have a field LeadsByActiveContacts which has the value in [Unique Leads 1 Month] for that row divided by the value in [active contracts] for that row. (If COD_MARKET is italy, etc).
You can do what you want using Aggr, but it starts to get messy.
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 TimeFormat='h:mm:ss TT';
SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT';
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,
[Unique Leads last 6 Months],
[Unique Leads 1 Month],
[C:\Users\xyz\Desktop\Qlikview Tasks\Database Health Report\CRM Dashboard.xlsx]
(ooxml, embedded labels, table is Sheet2)