Set Analysis Identifier Not Working

[wlabarca99]

Community Members,

I am having an issue with a very large/complex Set Analysis Expression where I am trying to use the Identifier 1 in order to ignore all selections.  I tried using the Identifier 1 in all set analysis expressions as well as any SUM expressions but when I apply a filter I still get responsive results.  Please let me know what I am doing wrong.

Below is the expression:

(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),))+
SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(prdct_cst*quantity_returned),))+
SUM({1}AGGR(IF(Credit_Card_Accepted__c='Yes',(((IF(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),))>0,SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(prdct_cst*quantity_ordered),)),0)))*.02),0),asin))-
IF(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),))>0,SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(prdct_cst*quantity_ordered),)),0)-
SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered*prdct_fee),))-
(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,((quantity_ordered)*est_ref_fee),))-SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,((quantity_returned)*est_ref_fee),)))-
(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))*5)-
SUM({1}AGGR(IF(Free_Shipping__c='Yes',0,IF(Direct_Ship__c='Yes',0,
(IF(Standard_Shipping_Method__c='Small Parcel',SUM({1}Small_Parcel_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))),
IF(Standard_Shipping_Method__c='LTL Freight',SUM({1}LTL_Freight_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))),
IF(Standard_Shipping_Method__c='International',SUM({1}International_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))),)))))),asin))-
(SUM({1}AGGR(((SUM({1}Small_Parcel_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))))*0.75),asin)))-
SUM({1}AGGR(Incremental_Overhead_Percentage__c/100*SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),)),asin))-
(IF(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),))>0,SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(prdct_cst*quantity_ordered),)),0)*0.01)-
SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_returned),)))

Thanks!

[sunny_talwar · Accepted solution]

Can't verify if I got all of them fixed, but try this... the issue is related to some of the naked field not restricted using set analysis... highlighted one in red below, but there were others in the expression

=(
Sum({1<[cast_date.autoCalendar.Year] = {2019}>} If([cast_date.autoCalendar.Month] = 1, amount_sold)) +
Sum({1<[cast_date.autoCalendar.Year] = {2019}>} If([cast_date.autoCalendar.Month] = 1, prdct_cst*quantity_returned)) +
Sum({1} Aggr(If(Only({1} Credit_Card_Accepted__c) = 'Yes', (((If(Sum({1<[cast_date.autoCalendar.Year] = {2019}>} If([cast_date.autoCalendar.Month] = 1, amount_sold)) > 0, Sum({1<[cast_date.autoCalendar.Year] = {2019}>}If([cast_date.autoCalendar.Month] = 1, prdct_cst*quantity_ordered)), 0)))*.02), 0), asin)) -
If(Sum({1<[cast_date.autoCalendar.Year] = {2019}>}If([cast_date.autoCalendar.Month] = 1, amount_sold)) > 0, Sum({1<[cast_date.autoCalendar.Year] = {2019}>}If([cast_date.autoCalendar.Month] = 1, prdct_cst*quantity_ordered)), 0) -
Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered*prdct_fee),))-
(Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,((quantity_ordered)*est_ref_fee),))-SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,((quantity_returned)*est_ref_fee),)))-
(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))*5)-
SUM({1}AGGR(IF(Only({1} Free_Shipping__c)='Yes',0,IF(Only({1} Direct_Ship__c)='Yes',0,
(IF(Only({1} Standard_Shipping_Method__c)='Small Parcel',SUM({1}Small_Parcel_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))),
IF(Only({1} Standard_Shipping_Method__c)='LTL Freight',SUM({1}LTL_Freight_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))),
IF(Only({1} Standard_Shipping_Method__c)='International',SUM({1}International_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))),)))))),asin))-
(SUM({1}AGGR(((SUM({1}Small_Parcel_Cost_Per_Pound__c)*(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_ordered),))-Sum({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(quantity_returned),))))*0.75),asin)))-
SUM({1}AGGR(Only({1} Incremental_Overhead_Percentage__c)/100*SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),)),asin))-
(IF(SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),))>0,SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(prdct_cst*quantity_ordered),)),0)*0.01)-
SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_returned),)))

[marcus_malinow]

I'd suggest simplifying this by removing If conditions and replacing with equivalent set analysis. This will also improve performance.

An example

from

SUM({1<[cast_date.autoCalendar.Year]={2019}>}IF([cast_date.autoCalendar.Month]=1,(amount_sold),))

to

SUM({1<[cast_date.autoCalendar.Year]={2019},[cast_date.autoCalendar.Month]={1}>} amount_sold)