Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
avg({$<[Process Date]={">=$(=date(addmonths(CAMPAIGN_START_DATE,-6),'MM/DD/YYYY')) <$(=date(CAMPAIGN_START_DATE,'MM/DD/YYYY'))"}>}[Sales Amt])
Hi,
Can you explain more on your actual requirement?
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
What exactly are you trying to get and what object?
I am trying to get Avg sales from 6 months before campaign start date for each agent.
I am trying to get Avg sales from 6 months before campaign start date for each agent in a table
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.
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)
Can you please attach your qvw?
Check here.