Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?/? |
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, |
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 ....
will this work
load [Measure],
if(isnull(avg([Weight])),0,avg([ Weight])) as [RULE1AV_temp]
Resident [Weight ]
Group by [Measure];
It should work fine
Did you try?
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];
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
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 ?
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