Sum Prior and Current Date Help

[Anonymous]

Hi All,

I'm trying to get this sum prior and current date working. What I did, I assigned 2 variables for the current and prior date. Please help and see below expression.

Prior Date:

vMinDate=Max({<END_Date={"<$(=MonthStart(Max(END_Date)-1))"}>} END_Date)

Current Date:

vMaxDate=Max({<END_Date={"<$(=MonthStart(Max(END_Date)0))"}>} END_Date)

=sum({<END_Date = {">$(vMinDate)<=$(vMaxDate)"}, [Type]={'Income: Other'}>} [Amount])

where I try to accomplish here is to sum where <= '12/31/15' (vMaxDate)  > '11/30/15' (vMinDate) with  Type = 'Income: Other'

Many Thanks

Frank

[sinanozdemir · Accepted solution]

Hi,

Try with "=" inside the dollar sign expansions:

=sum({<END_Date = {">$(=vMinDate)<=$(=vMaxDate)"}, [Type]={'Income: Other'}>} [Amount])

[sunny_talwar]

Also I would add Date() function around the variables with the required formatting, because set analysis only works when LHS have the same formatting as RHS. Currently your RHS will be converted to Number when you use Max function with it:

Prior Date:

vMinDate = Date(Max({<END_Date={"<$(=MonthStart(Max(END_Date)-1))"}>} END_Date), 'YourDateFormatHere')

Current Date:

vMaxDate = Date(Max({<END_Date={"<$(=MonthStart(Max(END_Date)0))"}>} END_Date), 'YourDateFormatHere')

[Anonymous]

Thanks Sinan!!! works fine with the '=' added

[Anonymous]

Sunny - Thanks for the information.