Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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,
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
Hi Rahul,
could you share your qvf file? Imo you should modify your script and prepare your data for this measure.
Thanks.
What are you trying to do exactly?
I created a line table, "growth segment", with values, "High" and "Low". I have created a filter based on this. Now, I want that if I select "High" from my filter, my table should show territory names with above average sales growth and territory names with below average sales growth in case I select "Low".
Currently it's working if I select "High" but doesn't show any value in case the selection is "Low"
Could you please share sample app with sample data to test?
Try
if(GetFieldSelections([Growth Segment])='High',
Expression ,
if(GetFieldSelections([Growth Segment])='Low',
Expression,0
))
Hi Shraddha! could you figure out the error?
It seems you have made this expression really complicated, more than it needs to be.... can you try this
If(GetFieldSelections([Growth Segment]) = 'High',
Aggr(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]),
Aggr(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]))
It is still not showing me any territory for the "Low" selection.