Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
WE ARE LISTENING! New Navigation for Qlik Community, Sept. 26: TELL ME MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
wlabarca99
Contributor III
Contributor III

Set Analysis Identifier Not Working

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!

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

2 Replies
sunny_talwar

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
Specialist III
Specialist III

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)