Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
OK, all it needs is for the date to be correctly formatted to match your MonthYear. In the attached example, that means putting it in MMM YYYY format. If that's the format you're using, use this:
sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=date(addmonths(min(MonthYear),-count(distinct MonthYear)),'MMM YYYY'))"}>} Sales)
Otherwise substitute your own format.
Maybe this?
sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),1+max(MonthYear)-min(MonthYear)))"}>} Sales)
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
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.
Ok, I understand the point i will try this part and i tell you later, thanks
Carlos
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.
I will appreciate if you can help with this when you have time plz John, its very important this plz
Regards
Carlos
How about this?
sum({<Year=,Month=,MonthYear={"<$(=min(MonthYear)) >=$(=addmonths(min(MonthYear),-count(distinct MonthYear)))"}>} Sales)
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.