topic Re: Set Analysis Identifier Not Working in App Development

Set Analysis Identifier Not Working

wlabarca99 — Tue, 05 Feb 2019 16:01:26 GMT

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!

Re: Set Analysis Identifier Not Working

sunny_talwar — Tue, 05 Feb 2019 16:12:50 GMT

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

Re: Set Analysis Identifier Not Working

marcus_malinow — Tue, 05 Feb 2019 16:35:45 GMT

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

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