Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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?
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)
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!
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.