Comparing 2 fields in Set Analysis

[MRitter]

I don't know why this is not working.  I need to compare the 2 brand code fields and if they are equal then I calculate.

I have looked at both of these fields(transaction_brand_cd,Campaign_Brand_Code) in the 2 tables and the values should match.

I thought that I could create an association based on these fields but it creates a circular reference. 

Sum({<transaction_brand_cd ={"=$(=Campaign_Brand_Code)"},TotRetailQuantity = {">=1"},transaction_dt = {">=$(=CampaignStartDate)<=$(=CampaignEndDate)"},channel=,Tender=,caciquebuyer=>}TotRetailSalesAmt + TotRetailDiscountAmt + TotReturnSalesAmt))

If I change the = to -= then I get the full amount of sales.  When I use the = I get zero.

Thanks

[ajaykakkar93]

Hi,

I am not sure that this solution will work or not but you can still try this out : I am using possible values in set

Sum({<transaction_brand_cd=P(Campaign_Brand_Code),TotRetailQuantity = {">=1"},transaction_dt = {">=$(=CampaignStartDate)<=$(=CampaignEndDate)"},channel=,Tender=,caciquebuyer=>}TotRetailSalesAmt + TotRetailDiscountAmt + TotReturnSalesAmt))

[marcus_sommer]

Try it in this way (for more clarity I left the other conditions):

Sum({<transaction_brand_cd ={"=transaction_brand_cd=Campaign_Brand_Code"} >}
    rangesum(TotRetailSalesAmt, TotRetailDiscountAmt, TotReturnSalesAmt))

- Marcus

[MRitter]

Thank you.  Which approach is better and more efficient.  Yours or the one provided just prior to your suggestion?

[marcus_sommer]

Usually they aren't interchangeable because they are quite different. My suggestion compared two fields on a record-level like: if(Field1 = Field2, true(), false()) - of course only if they belong to the same table or are appropriate associated.

The suggestion from Ajay instead compared the Field1 with the possible values of the whole column of Field2.

Therfore it will depend on your requirements (and all the possible combinations of data) which approach is applicable.

- Marcus

[MRitter]

Thank you for responding.

The fields are in 2 different tables.  They are not directly associated.

I currently only have 2 values for Brand.  So not a lot of combinations.