Set analysis range

[#1]

Dear all,

I have a question regarding the use of date ranges in set analysis.

When using the following syntax I'm getting 4 results as return

Serial	Date	Price
A	01/01/2013	10000
B	16/12/2012	9000
C	10/10/2012	9500
D	30/10/2012	8000

sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum))"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

,Year=,Month=,Quarter= >}MachinePurchaseCost)

MonthYearNum will return 201301 as a number.

What I wanted to achieve is to have a date range for example every serial which has a date between the month/year I've selected. (eg: Jan/2013). When I choose this, I'm counting back 2 months. So I will get All serials between November 2012 and January 2013.

This is giving me 201211: Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00')

However, when I integrate these two syntaxes I'm getting nothing as result. My end result should be Serial A and B.

sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum)) >= Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00')"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

,Year=,Month=,Quarter= >}MachinePurchaseCost)

Am I converting something wrong?

I appreciate the help.

Kind regards

[#2]

I'm not entirely sure why that expression isn't working - maybe it's because the & converts things into a string, which isn't being read as a number? If so you could try 100*year + Month and see if that helped.

However, it might be easier to go for the expression:

Date={">=$(=num(addmonths(vMonthYear, -2))) <=$(vMonthSelected)"}, I.E. Why use MonthYearNum if you don't have to?

[fosuzuki · Accepted solution]

I think you should've included the second part inside a dollar-sign expression:

sum({$<MonthYearNum ={"<= $(=Max(MonthYearNum)) >= $(=Year(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)) & Num(Month(addmonths(Date(MonthEnd($(vMonthYear)),'DD/MM/YYYY'),-2)),'00'))"},MachineStatus = {'Consigment','Demonstration','Free', 'Ordered', 'Purchasing', 'Reserved'}

,Year=,Month=,Quarter= >}MachinePurchaseCost)

[#4]

Not completely sure why it didn't work when I tried it. I probably made a typo mistake in my expression but this did the trick.

Thanks!

[#5]

I've been using MonthYearNum quite a bit. But it's indeed easier to use the expression you said. Both expressions give me the correct results.

Thanks for the hint though. This makes my expression shorter and easier to understand.