MonthYear - How to compare selected period with previous period

[#1]

Hi all,

Doing a comparison on selected period with previous period is straightforward if we use two time selectors, one for year and one for month.

But how to do it if we only use one selector that contains MonthYear, I,e MM-YYYY, ex. 03.2015?

My problem is that I cannot get the previous month period specified in the set analusis.

The expression must look something like this:

=sum( {$ <
[CalendarTable MonthYear] = , // I need to reset the MponthYear because I can not do calculation on this field, i.e minus one year, 03-2015 - 1 does not work
     [CalendarTable Year] = {$(=Only([CalendarTable Year]) - 1 )}, // I deduct one year
     [CalendarTable Month] = {$(=[CalendarTable Month]) }      // but I can not find a way to express the month selection
>} Sales )

See attached qvw-file.

Any help would be very appreciated,

Thanks

[Anonymous]

without testing with your app, did you try

[CalendarTable MonthYear] = {$(=addmonths(only([CalendarTable MonthYear],-12))}

this shouldgive you the same month 1 year before

[swuehl]

You might need to format the returned value from addmonths():

=sum( {$ <

  [CalendarTable MonthYear] = {"$(=Date(AddMonths([CalendarTable MonthYear],-12),'MM-YYYY'))"} ,

  [CalendarTable Year] = ,//{$(=Only([CalendarTable Year]) - 1 )},

  [CalendarTable Month] = //{$(=[CalendarTable Month]) }     

>} Sales )

[jonathandienst]

I usually include a month sequence number in my master calendar, something like

     LOAD CalDate,

          Month(CalDate) As Month,

          Year(CalDate) As Year,

          Year(CalDate)*12 + Month(CalDate) As MonthSeq,

          ...

Now you can select 12 months ago by simple arithmetic:

     <MonthSeq = {"$(=Max(MonthSeq)-12)"}>

[#5]

Thanks for the suggestions, and all suggested solutions works if I just want to look at one single month, ex. March 2015.

But if I want to see YTD March 2015 vs YTD March 2014 by selecting the three months 01-2015, 02-2015, 03-2015 in the listbox, then none of these methods works.

[swuehl · Accepted solution]

You can use a numeric search:

=sum( {$ <

  [CalendarTable MonthYear] = {">=$(=Date(AddMonths(min([CalendarTable MonthYear]),-12),'MM-YYYY'))<=$(=Date(AddMonths(max([CalendarTable MonthYear]),-12),'MM-YYYY'))"} ,

  [CalendarTable Year] = ,

  [CalendarTable Month] =

>} Sales )

[#7]

Upload of app where swuehl's solution is implemented :