topic Re: Addmonths with max date in QlikView

Addmonths with max date

Sat, 01 Mar 2014 23:53:28 GMT

Hi I'm trying to use the addmonths function, but apparently can't get the syntax right. Any help is greatly appreciated!

=AddMonths({$<(=max(vDate))>}, -1, 1)

Re: Addmonths with max date

MK_QSL — Sun, 02 Mar 2014 00:18:43 GMT

If vDate is variable

AddMonths('$(vDate)', 1)

This will add one month to your vDate

If vDate is normal date

AddMonths ('max(vDate )', 1)

Try both of the above formula with and without single quotes

I. E. ''

In Set Analysis.... Something like below

Sum({$<Month = {'$(=AddMonths(max(vDate)), 1)'}>}Sales)

Re: Addmonths with max date

Sun, 02 Mar 2014 01:04:44 GMT

Thank you! That isolated addmonths function now works. But to your last set analysis example, how would I correct the following expression I'm using in a chart (focusing on the avg function):

//find average of the field Sales for when the field Vintage is between the latest date and 2 years before

avg({$<Vintage={">=$(=AddMonths(max(Vintage),-24,1))<=$(=max(Vintage))"}>} [Sales])

Needless to say, I'm struggling with the nested functions.

Re: Addmonths with max date

MK_QSL — Sun, 02 Mar 2014 08:02:10 GMT

Can you share your sample file or apps?

Re: Addmonths with max date

Sun, 02 Mar 2014 14:54:17 GMT

Unfortunately I can't (size and proprietary). But the structure looks like this:

INLINE [

Vintage, Sales, State

10/31/2009, 124303, Texas

10/31/2009, 84902, Florida

11/30/2009, 372928, California

11/30/2009, 84449, Ohio

12/31/2009, 48272, Indiana

];

I have a chart with the State as a dimension, and the vintages run for about 5 years (with all values at end of month). Trying to find the average sales over the past 24 months in a given state. Thanks!