need help

[yogitamehta10]

Hi,

I have this Expression at the front end . i'm trying to do all this calculation at the backend, any idea how should i proceef

if(count(distinct if([ Measure Code] = 'EI' and [Measure Date] >= Today()-$(LookbackDays) and [Measure User ] = '$(User)',[Measure Site Co]))= 0,0,

           if(sum(if( Measure Code] = 'EI' and [Measure Date] >= Today()-$(LookbackDays) and [ Measure User ] = '$(User)' and Upper(Text([Measure Control]))<> 'N',[ Measure Population]))<>0,

           1-(sum(if([Measure Code] = 'EI' and [Measure Date] >= Today()-$(LookbackDays) and [Measure User ] = '$(User)' and Upper(Text([Measure Control]))<> 'N',[Measure Exceptions]))

             /sum(if([HM Measure Code] = 'EIM08' and [Measure Date] >= Today()-$(LookbackDays) and [Measure User] = '$(User)' and Upper(Text([Measure Control]))<> 'N',[Measure Population]))),0))

               *$(EIM08DW)

+

if(count(distinct if([Measure Code] = 'ESM04' and [Measure Date] >= Today()-$(LookbackDays) and [Measure User ] = '$(User)',[Measure Site ]))= 0,0,

           if(sum(if([Measure Code] = 'ESM04' and [Measure Date] >= Today()-$(LookbackDays) and [Measure User ] = '$(User)' and Upper(Text([Measure Control]))<> 'N',[Measure Population]))<>0,

           1-(sum(if([Measure Code] = 'ESM04' and [Measure Date] >= Today()-$(LookbackDays) and [Measure User Type] = '$(User)' and Upper(Text([Measure Control]))<> 'N',[Measure Exceptions]))

             /sum(if([Measure Code] = 'ESM04' and [Measure Date] >= Today()-$(LookbackDays) and [Measure User Type] = '$(User)' and Upper(Text([Measure Control]))<> 'N',[Measure Population]))),0))

               *$(ESM04DW)

[kuczynska]

You should probably start from moving things like Upper(Text([Measure Control])) to the backend, it will speed up the performance.

Second thing - maybe you should think about splitting the big frontend expression into smaller chunks and run your frontend expression over flags created in the backend?

At this stage it's not so easy to determine what parts we can move to the backend - it depends on the way you would create your flag fields (make them return only 0 or 1, that will increase the calculation in the frontend quiet significantly than using string values) and for example if you have your variables defined in the script as well.

It would be great of you could give some sample data with qvw

[marcus_sommer]

How your expression looks like I think it will be quite difficult to transfer it within the script and will need (some) efforts. Most often are such expressions better placed within the gui then in the script and only if they are after optimizing really too slow makes it sense to transfer them within the script.

And I think you expressions could be optimized a lot. For me it looked that the query if the count = 0 or the sum <> 0 isn't really necessary and the conditions could be placed within a set analysis, maybe like this:

rangesum(

sum({<

     [Measure Code] = {'EI'},

     [Measure Date] = {">=$(=Today()-$(LookbackDays))"},

     [Measure User ] = {'$(User)'},

     [Measure Control] -= {'N', 'n'} >} [Measure Exceptions]) /

alt(sum({<

     [Measure Code] = {'EI'},

     [Measure Date] = {">=$(=Today()-$(LookbackDays))"},

     [Measure User ] = {'$(User)'},

     [Measure Control] -= {'N', 'n'} >} [Measure Population]), 1) *$(EIM08DW),

sum({<

     [Measure Code] = {'ESM04'},

     [Measure Date] = {">=$(=Today()-$(LookbackDays))"},

     [Measure User ] = {'$(User)'},

     [Measure Control] -= {'N', 'n'} >} [Measure Exceptions]) /

alt(sum({<

     [Measure Code] = {'ESM04'},

     [Measure Date] = {">=$(=Today()-$(LookbackDays))"},

     [Measure User ] = {'$(User)'},

     [Measure Control] -= {'N', 'n'} >} [Measure Population]), 1) *$(ESM04DW))

I think this could be a good starting point for you to check and adjust these approach - step by step, at first each expression-part as single expression - until the results are correct.

- Marcus