second part of if condition not working

[Anonymous]

Hi All,

My aim is to get territory names on field selection- 'High', if Sales growth of the territory is greater than avg sales growth and to get territory names on field selection- 'Low', if Sales growth of the territory is lower than avg sales growth. Please check the code provided below:

if(GetFieldSelections([Growth Segment])='High',

if((((AGGR(sum(if([Semester]='S22016',[Total Sales],if([Semester]='S12017',[Total Sales],0))),[Territory Name]))-

(AGGR(sum(if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0))),[Territory Name])))/

(AGGR(sum(if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0))),[Territory Name])))>

((sum(total if([Semester]='S22016',[Total Sales],if([Semester]='S12017',[Total Sales],0))))-

(sum(total if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0)))))/

(sum(total if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0)))),[Territory Name],0),

if((((Aggr(sum(if([Semester]='S22016',[Total Sales],if([Semester]='S12017',[Total Sales],0))),[Territory Name]))-

(AGGR(sum(if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0))),[Territory Name])))/

(AGGR(sum(if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0))),[Territory Name])))<

((sum(total if([Semester]='S22016',[Total Sales],if([Semester]='S12017',[Total Sales],0))))-

(sum(total if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0)))))/

(sum(total if([Semester]='S22015',[Total Sales],if([Semester]='S12016',[Total Sales],0)))),[Territory Name],0))

It is working fine for the field selection-'High' but not for low. Can anybody guide me where am I going wrong? Thanks.

Best Regards,

[Anonymous]

Ohh. So basically here, why are we aggregating the complete thing at territory name level? Will it work if I just use growth segment?

[Anonymous]

Understood my doubt. We need to aggr it 1st at terr level to get counts at territory level and then at the segment level. It makes sense. Also, the code works perfectly. Thanks a lot for all your help.

[sunny_talwar]

Territory Name is the main aggregation needed to carry out your Sum([Total Sales]) calculation... whereas Growth Segment is mainly needed to carry out the if statement....