topic Re: Problem with Addmonths Function in QlikView

Problem with Addmonths Function

Fri, 30 Sep 2011 14:01:48 GMT

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

Re: Problem with Addmonths Function

Sat, 01 Oct 2011 16:40:04 GMT

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.

Problem with Addmonths Function

swuehl — Sun, 02 Oct 2011 12:37:07 GMT

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

Problem with Addmonths Function

Mon, 03 Oct 2011 18:24:23 GMT

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.