Converting if/then to set analysis

[#1]

I am trying to convert this perfectly working If/Then to set analysis:

=Sum(If(Date >= DateEffStart and Date<= DateEffEnd, Aggr(Sales,SalesIndex)))

I have tried the following which almost works, but appears to select the wrong dates:  (it is searching through a slowly changing date table)

=Sum({$<[Date]={">=$([DateEffStart])<=([DateEffEnd])"}>} Aggr(Sales,SalesIndex))

Is this even possible with set analysis?   The if/then does work perfectly. 

Thanks very much for any insight!

[Anonymous]

try this

=aggr(Sum({$<[Date]={">=$([DateEffStart])<=([DateEffEnd])"}>}Sales),SalesIndex))

[Caique_Zaniolo]

Have you tried this?

Sum(Aggr(Sum( {$<[Date]={">=$([DateEffStart])<=$([DateEffEnd])"}>} Sales),SalesIndex))

[#4]

Unfortunately this did not work.    I tried it.  No value returned. 

[#5]

This is working the same way as the one i first posted.  It is returning the right sum, unfortunately when applying the total to other dimensions it seems to grab the first diminsion as opposed to the dimension of the date.

For example, if we are dealing with:

Office    DateEffStart     DateEffEnd

Miami     1/1/2011           6/1/2011

Chicago  6/2/2011          1/1/6099

Then totaling up the sales number by comparing the sales Date to the EffStart and end dates, if i using OFFICE as the dimension and the date on all the sales is 6/2/2011 or greater, the Office is coming back as Miami.  It should be Chicago.   The if/then format correctly returns the office Chicago on the Sum(sales) number.  

I am not sure why set analysis seems to just grab the first value, Miami in this case, as opposed to returning Chicago.

Thanks SO much for your help everyone!

[chematos]

Try this:

=aggr(Sum({$<[Date]={'>=$(=[DateEffStart]) <= $(=[DateEffEnd])'}>}Sales),SalesIndex))

Make sure format date fields is the same for each one