How to compare two different field in set expression?

[anindya_manna]

Hello Experts,

I have a table like below.

Process Date	CAMPAIGN_START_DATE	Sales	Agent ID
1/1/2018	2/1/2015 12:00:00 AM	500	111
1/2/2018	5/1/2015 12:00:00 AM	600	222
1/3/2018	5/13/2015 12:00:00 AM	700	333
1/4/2018	5/21/2015 12:00:00 AM	800	444
1/5/2018	5/29/2015 12:00:00 AM	900	555
1/6/2018	6/2/2015 12:00:00 AM	1000	666
1/7/2018	6/3/2015 12:00:00 AM	1100	777
1/8/2018	6/9/2015 12:00:00 AM	1200	888
1/9/2018	6/18/2015 12:00:00 AM	1300	999

Below code not working for me.

avg({$<[Process Date]=P({">=$(=addmonths(Max(CAMPAIGN_START_DATE),-6)) <=$(=date(Max(CAMPAIGN_START_DATE),'MM/DD/YYYY'))"})>}[Sales Amt])

I think two different field cant be compare in set exp.So please suggest any alternative idea inclding IF condition.

I dont have access to the script part so cant create any additional flag

[anindya_manna · Accepted solution]

avg({$<[Process Date]={">=$(=date(addmonths(CAMPAIGN_START_DATE,-6),'MM/DD/YYYY')) <$(=date(CAMPAIGN_START_DATE,'MM/DD/YYYY'))"}>}[Sales Amt])