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,

[sunny_talwar]

Are you sure there are Territory Name which meet this criteria?

Sum({<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum({<[Semester] = {'S22015', 'S12016'}>} [Total Sales]) <

Sum(TOTAL {<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum(TOTAL {<[Semester] = {'S22015', 'S12016'}>} [Total Sales])

[Anonymous]

Yes. Also, I am comparing it with the average sales growth so it should have the values. I have shared a sample data set in this thread if you can take a look. Thanks for your help though.

[sunny_talwar]

I saw the sample dataset, but how is this loaded into your dashboard? Everything is a field or do you use CrossTable Load?

[Anonymous]

I did not use crosstable load. I imported the data directly using Add Data.

[sunny_talwar · Accepted solution]

This seems to have worked

=Aggr(If(GetFieldSelections([Growth Segment]) = 'High',

If(

Sum({<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum({<[Semester] = {'S22015', 'S12016'}>} [Total Sales]) >

Sum(TOTAL {<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum(TOTAL {<[Semester] = {'S22015', 'S12016'}>} [Total Sales]), [Territory Name]),

If(

Sum({<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum({<[Semester] = {'S22015', 'S12016'}>} [Total Sales]) <

Sum(TOTAL {<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum(TOTAL {<[Semester] = {'S22015', 'S12016'}>} [Total Sales]), [Territory Name])), [Territory Name])

Here Growth Segment is a field from an Island table

[Anonymous]

Wow! It's working.. Thanks a lot. I still need to understand the code but it's working. Thanks a ton!

[sunny_talwar]

You should have a much easier time understanding this expression because at least to me, it looks much easier to read then the one you initially had....

[Anonymous]

Yes, I understand. The thing is I am pretty new to Qliksense and haven't explored the set analysis as yet.

[Anonymous]

If I use the similar code in a table it again has the same problem. I have used growth segments as dimensions and the code calculation as a measure. Following code is used:

sum(Aggr(If([Segments] = 'High',

If(

Sum({<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum({<[Semester] = {'S22015', 'S12016'}>} [Total Sales]) >

Sum(TOTAL {<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum(TOTAL {<[Semester] = {'S22015', 'S12016'}>} [Total Sales]), count(distinct [Territory Name])),

If(

Sum({<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum({<[Semester] = {'S22015', 'S12016'}>} [Total Sales]) <

Sum(TOTAL {<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum(TOTAL {<[Semester] = {'S22015', 'S12016'}>} [Total Sales]), count(distinct [Territory Name]))), [Territory Name]))

It shows a number for High but not for Low.. Can you please let me know why this keeps on happening ? So I can get it right by myself the next time.. Thanks.

Best Regards,

[sunny_talwar]

You need to add Growth Segment to your Aggr() function

Sum(Aggr(If([Growth Segment] = 'High',

If(

Sum({<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum({<[Semester] = {'S22015', 'S12016'}>} [Total Sales]) >

Sum(TOTAL {<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum(TOTAL {<[Semester] = {'S22015', 'S12016'}>} [Total Sales]), count(distinct [Territory Name])),

If(

Sum({<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum({<[Semester] = {'S22015', 'S12016'}>} [Total Sales]) <

Sum(TOTAL {<[Semester] = {'S22016', 'S12017'}>} [Total Sales])/Sum(TOTAL {<[Semester] = {'S22015', 'S12016'}>} [Total Sales]), count(distinct [Territory Name]))), [Territory Name], [Growth Segment]))