Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

second part of if condition not working

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,

22 Replies
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
Not applicable
Author

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
Not applicable
Author

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

sunny_talwar

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])

Capture.PNG

Here Growth Segment is a field from an Island table

Anonymous
Not applicable
Author

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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]))


Capture.PNG