Problem with Addmonths Function

[#1]

Hello,

I recently put together an addmonths function for a prior month calculation (addmonths to enable me to cross years).

The formula worked great and then on a refresh no longer worked.  I will show you the steps I have taken.

The original formula which worked is below:

if(getselectedcount(Month)=1 ,

1*(sum({<Quarter=,Year=,Month=,MonthYear={"<=$(=Date(addmonths(Max(MonthYear),-1), 'MMM-YYYY'))"}>}Value)/$(vScale)),

1*(sum({<Quarter=,Year=,Month=,MonthYear={">=$(=Date(addmonths(Max(MonthYear),-(2*getselectedcount(Month)-1)), 'MMM-YYYY'))<=$(=Date(addmonths(Max(MonthYear),-(getselectedcount(Month))), 'MMM-YYYY')) "}>}Value)/$(vScale))))

On the refresh nothing worked.  I ended up taking the "<=" out of the first line and getselectedcount(Month)=1 now works, but I still can't get the second part to work.

I have no idea why these would work initially and then on a refresh not work, I have checked files and nothing seems to have changed. 

Any help or advice on the formulas above would be greatly appreciated.

Thank you,

Craig

[#2]

Craig,

Generally when you reload all your selections are cleared unless you lock them. However this is not the problem you are having. What are the outputs for getselectedcount(Month) and date functions used in else part?

Regards,

Kiran.

[swuehl]

Craig,

may I ask, what you want to achieve with something like:

MonthYear={">=$(=Date(addmonths(Max(MonthYear),-(2*getselectedcount(Month)-1)), 'MMM-YYYY'))

if you select nothing in MonthYear, getselectedcount will be zero, and the second parameter will be +1, addmonths will then try to access a date in the future, outside your current selection (max(MonthYear)+1), you will get no result then, I believe.

If you select more than 1 value, you are going 3, 5, 7, 9 .. Months back in time, is that what you want?

Regards,

Stefan

[#4 · Accepted solution]

the formula I shared was actually the alternative to If(getselectedcount(Month)=0, which would take care of your first issue.

I did find out what happened.

When I separated the formula to work on it it was on separate lines

ie:

<= x

>=y

and this worked in my copy, but on the refresh it broke, but when i put it all on one line <=x >=y it worked again.  A simple solution--I didnt realize a refresh would change how QV would read a formula.