Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
=$(LeadsByActiveContacts) ?
/EDIT:
SO, your problem is, I assume in your code you're doing something like:
TableName:
LOAD
Things,
Stuff,
Dimensions,
etc,
sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts])) as LeadsByActiveContacts
Resident someplace
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.
Edit script code is fine.. what is issue there?
i want to refer the Edit Script code in the Expression. how do i do this?
Script itself ran, but the final output is not matching up? What is the issue we are trying to troubleshoot here?
Hi there,
Have you tried to remove the equal symbol in the expression. You can try to put a expression like this: LeadsByActiveContacts/1.
Hope it helps
Regards,
MB
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
=$(LeadsByActiveContacts) ?
/EDIT:
SO, your problem is, I assume in your code you're doing something like:
TableName:
LOAD
Things,
Stuff,
Dimensions,
etc,
sum(if(COD_MARKET='Italy',[Unique Leads 1 Month]))/sum(if(COD_MARKET='Italy',[Active Contacts])) as LeadsByActiveContacts
Resident someplace
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.
There is no better way to explain it. In simple terms i am asking how do i refer the EditScript code in this case in the Expression in Frontend
you should try below in expression
=sum(LeadsByActiveContacts)
or any other relevant aggregation function
I tried this already but sum(LeadsByActiveContacts) changes the final output that is the reason i just want to refer it as is in the frontend . Is there a way to do it without use of sum?
Since the code is not converted into a field, all you have to do is refer to it as a field. May be first in a table box object to check if the numbers are making sense or not