Epression changes

[yogitamehta10]

Hi,

I have this expression in front end for every chart , now i want to transfer this to backend scripting for better performance or i can use set analysis

any suggections ?/?

if(isnull(avg(if([Measure] = 'E03',[ Weight]))),0,avg(if([Measure] = 'E03',[Weight])))

+

if(isnull(avg(if([Measure] = 'E04',[Weight]))),0,avg(if([Measure] = 'E04',,[Weight])))

+

if(isnull(avg(if([Measure] = 'E05',[Weight]))),0,avg(if([Measure] = 'E05',,[Weight])))

+

if(isnull(avg(if([Measure] = 'E06',[ Weight]))),0,avg(if([Measure] = 'E06',,[Weight])))

+

if(isnull(avg(if([SM Measure] = 'EP07',[ Weight]))),0,avg(if([Measure] = 'EP07',,[Weight])))

+

if(isnull(avg(if([Measure] = 'EP08',[Weight]))),0,avg(if([ Measure] = 'EP08',,[Weight]))) any suggections ?/?

[gautik92]

In mapping Table

Mapping Mapping Name

Load

     Measure,

If(if(isnull(avg(if([Measure] = 'E03',[ Weight]))),0,avg(if([Measure] = 'E03',[Weight])))

+

if(isnull(avg(if([Measure] = 'E04',[Weight]))),0,avg(if([Measure] = 'E04',,[Weight])))

+

if(isnull(avg(if([Measure] = 'E05',[Weight]))),0,avg(if([Measure] = 'E05',,[Weight])))

+

if(isnull(avg(if([Measure] = 'E06',[ Weight]))),0,avg(if([Measure] = 'E06',,[Weight])))

+

if(isnull(avg(if([SM Measure] = 'EP07',[ Weight]))),0,avg(if([Measure] = 'EP07',,[Weight])))

+

if(isnull(avg(if([Measure] = 'EP08',[Weight]))),0,avg(if([ Measure] = 'EP08',,[Weight])))) as alias name,

[jonathandienst]

You can simplify this a lot - RangeSum treats nulls as zeroes:

RangeSum(

  avg(if(Measure = 'E03', Weight)),

  avg(if(Measure = 'E04', Weight)),

  avg(if(Measure = 'E05', Weight)),

  avg(if(Measure = 'E06', Weight)),

  avg(if(Measure = 'EP07', Weight)),

  avg(if(Measure = 'EP08', Weight))

) As newField,

You will need a Group By for any non-aggregated fields in the LOAD expression

FROM ....

Group By ....

[yogitamehta10]

will this work

load [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE1AV_temp]

Resident [Weight ]

Group by [Measure];

[sasiparupudi1]

It should work fine

[qlikviewwizard]

Did you try?

[jonathandienst]

It should, but this will be more efficient as it calculates the averages once each rather than twice:

load [Measure],

Alt(avg([Weight])),0) as [RULE1AV_temp]

Resident [Weight ]

Group by [Measure];

[Kushal_Chawda]

Try this on front end.

=RangeAvg(avg({<[Measure] = {'E03'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E04'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E05'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E06'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E07'}>}[ Weight]),0)+

RangeAvg(avg({<[Measure] = {'E08'}>}[ Weight]),0)

from back end you can do this as suggested by Jonathan

[yogitamehta10]

oad [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE1AV_temp]

where match([Measure],'EPM02',

' EPM03',

'EPM04',

' EPM05',

' EPM06',

Resident [Weight ]

Group by [Measure];

i used this  but now i have issue i have  another expression with same type but mesaure  match are diifferenrt for that .

lets say  measure ='EP90', 'Ep34' and we will say it [RULE2AV_temp]

i want that to include in same script

how should i do ?

[Kushal_Chawda]

create the Flag in Script for RULE1AV & RULE2AV like below

load [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE1AV_temp],

'RULE1AV' as Flag

where match([Measure],'EPM02',

'EPM03',

'EPM04',

'EPM05',

'EPM06')

Resident [Weight ]

Group by [Measure];

concatenate

load [Measure],

if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE2AV_temp],

'RULE2AV' as Flag

where match([Measure],'EPM90',

'EPM34')

Resident [Weight ]

Group by [Measure];

Then in front end use the Flag in expression

avg({<Flag={'RULE1AV'}>}[ Weight]) +

avg({<Flag={'RULE2AV'}>}[ Weight])

modify your expression as per your need