Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Replace Expression code with EditScript code

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

1 Solution

Accepted Solutions
richnorris
Creator II
Creator II

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.

View solution in original post

17 Replies
Kushal_Chawda

Edit script code is fine.. what is issue there?

Not applicable
Author

i want to refer the Edit Script code in the Expression. how do i do this?

sunny_talwar

Script itself ran, but the final output is not matching up? What is the issue we are trying to troubleshoot here?

miguelbraga
Partner - Specialist III
Partner - Specialist III

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

richnorris
Creator II
Creator II

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.

Not applicable
Author

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

Kushal_Chawda

you should try below in expression

=sum(LeadsByActiveContacts)


or any other relevant aggregation function

Not applicable
Author

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?

sunny_talwar

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