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

[HarishG]

Hi,

Can you explain more on your actual requirement?

[olivierrobin]

hello

be careful f the format of date fields

try to put the formulas in text boxes to verify that the fields have correct format

i wouls suppose that Process Date is not in M/D/YYYY format

[sunny_talwar]

What exactly are you trying to get and what object?

[anindya_manna]

I am trying to get Avg sales from 6 months before campaign start date for each agent.

[anindya_manna]

I am trying to get Avg sales from 6 months before campaign start date for each agent in a table

[vishsaggi]

hello Anindya,

Few points here to clarify.

1. Your date formats between Processed Date and Campaign Start Date are different we need to convert them. However, your expr will show 0 or nothing coz your dates are not between the processed dates. Could be that you just gave sample data which is not matching.

2. Your sales field name is different form the data sample to that in the expression. Like in your expr you have [Sales Amt] but your sample data set has a field name Sales.

However, I have created few date ID's to use them in your set analysis please have a look if this is of any help.

Can we know why you are using P() here just wanted to understand.

Retrospective:
LOAD *, Floor(Date#(ProcessDate, 'M/D/YYYY')) AS ProcessDtID,
Floor(Date(Date#(CAMPAIGN_START_DATE, 'M/D/YYYY hh:mm:ss ttt'), 'M/D/YYYY')) AS CampaignDtID,
Date(Date#(CAMPAIGN_START_DATE, 'M/D/YYYY hh:mm:ss ttt'), 'M/D/YYYY') AS CampaignStartDt  INLINE [
ProcessDate, 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/2015, 5/29/2015 12:00:00 AM, 900, 555
1/6/2015, 6/2/2015 12:00:00 AM, 1000, 666
1/7/2015, 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
];

I used text object to check Avg sales. Changed few Process Dates to 2015 as all of them were in 2018 which always display 0 for Avg. so changed few dates that we can get some values. Check screenshot below.

[sasiparupudi1]

I think the problem you have is you have strings in date than numeric dates..

May be try like below

avg({<ProcessDate={">=$(=Date(Addmonths(Floor(Date#(MAXString(CAMPAIGN_START_DATE), 'M/D/YYYY hh:mm:ss ttt')),-6), 'M/D/YYYY'))"}>}Sales)

[anindya_manna]

Can you please attach your qvw?

[vishsaggi]

Check here.