Problem with a formula

[#1]

Hi everyone:

I'm using this formula in set analysis to calculate the sales of my previous selected month:

SUM({$<Year= {$(=max(Year) - $(=if(max(Month) = 1, 1, 0)))},Month = {$(=if((Month) = 1, 12, only(Month)-1) )}, MonthYear = {"<=$(=max(MonthYear))"}>} Sales)

Does anyone can help me to modify this formula´, i need to show the sales of "N" Previous Months, because mine it only works when you select 1 month???

For example, if I select DEC,NOV,OCT of 2009, the formula has to show JUL,AUG,SEP of 2009

Regards

Carlos

[johnw]

Maybe this?

sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),1+max(MonthYear)-min(MonthYear)))"}>} Sales)

[#3]

Almost John, with that formula you can get the sales up to the previous month of the selection, what i attempt to do is to get the sales for the same number of the month's selection but the previous one, may i make myself understable???

Example :

My Selection = JAN 2010, FEB 2010

Desire Column = NOV 2009, DEC 2009

Regards

Carlos

[johnw]

The formula I posted is intended to do that. Note this portion of it:

sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),1+max(MonthYear)-min(MonthYear)))"}>} Sales)

1+max(MonthYear)-min(MonthYear) is calculating how many months are in the range you selected. We then subtract that many months from min(MonthYear). That gives us the starting point for the range.

Is it not working? It might have a bug in it, since I can't test it myself. I would suggest breaking down the part I highlighted above, and trying to figure out where it is going wrong.

[#5]

Ok, I understand the point i will try this part and i tell you later, thanks

Carlos

[#6]

I think here is the problem John but i dont get why the year goes to 2012, look i put this part of the formula in a chart to see the results and look:

any ideas???

[johnw]

Oh! Of course! MonthYear is a date. So 1+max(MonthYear)-min(MonthYear) is giving the DAYS between the two, not the MONTHS.

Hopefully you can debug from there. Very busy right now. Will debug later if you haven't.

[#8]

I will appreciate if you can help with this when you have time plz John, its very important this plz

Regards

Carlos

[johnw]

How about this?

sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),-count(distinct MonthYear)))"}>} Sales)

[#10]

It is the same result of the other formula, it doesnt work, i put this:

addmonths(min(MonthYear),-GetSelectedCount(MonthYear))

and i can get the desire period of time but the formula doesnt work, i mean when i select JAN 2010, FEB 2010 i get DIC 2009, NOV 2009

but without the sum of the sales.