Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am trying to use a set analysis as an expression where I need to limit the select to a specific month. I cant seem to get the date syntax correct.
I used the below script to change the desired date from dd/mm/yyyy to MM-YYYY
Date(monthstart(CloseDate), 'MM-YYYY') as CloseDate_MY;
This part works fine.....but now that the date is in that format...how do you write the desired month in the set analysis expression. My expression is below:
sum({ $<CloseDate_MY={052011}>} Opportunity.Amount) ( I need to limit the date to May 2011
Thanx
I guess you need to change the script to:
Date(monthstart(CloseDate), 'MMYYYY') as CloseDate_MY
or the set analysis to sum({ $<CloseDate_MY={05-2011}>} Opportunity.Amount)
or you can break the date in two fields:
year(CloseDate) as CloseYear,
num(month(CloseDate)) as CloseNMonth
and set analysis to:
sum({ $<ClsoeYear={2011}, CloseNMonth={5}>} Opportunity.Amount)
Hope it helps,
Erich
I would advice to create a Flag in the script named "ClosedDtFlag" and marked it against the Date/Month/Year with value as 1or 0 and in expression you can simply write as
Normal Expression:-
SUM(Opportunity.Amount*ClosedDTFlag) or
Set Analysis:-
SUM({<ClosedDTFlag={1}> Opportunity.Amount})
Hope this helps