Problem With Prior N Months Expression Using Only and Autonumber
I need to be able to sum sales over rolling quarters and rolling 12 months when user selects one month. I understood that I could use autonumber on my date dimension to generate numbers to use for this. Alas, if I don't cross a year boundary, I can get an expression that calculates fine using the month number field which is loaded (sorted) directly from sql server. However, when apply the same technique to a field generated by autonumber it does not work. I just get the sales for the currently selected month. I have reduced the problem to just the currently selected month and the prior month for simplicity and formulated inline loads for completeness. A basic .qvw file is attached.
My expressions are:
sum( {$<[YearMonthNumber]+={ $(#=Only([YearMonthNumber])-1) }>} [Amount] ) // does not work
sum( {$<[MonthOfYear]+={ $(#=Only([MonthOfYear])-1) }>} [Amount] ) // works
My load script is:
[Date]:
Load
Year, MonthOfYear, YearMonth, autonumber(YearMonth) as YearMonthNumber;
load * inline [
Year, MonthOfYear, YearMonth
2011,6,'2011-06'
2011,7,'2011-07'
2011,8,'2011-08'
];
[Sales]:
load * Inline [
YearMonth, Amount
'2011-06',10
'2011-07',10
'2011-08',10];
The attached dashboard shows that the YearMonthNumber is being generated as I anticipated. Output from table:
MonthOfYear
Year
YearMonth
YearMonthNumber
Amount
6
2011
2011-06
1
10
7
2011
2011-07
2
10
8
2011
2011-08
3
10
Note that my autonumber generated field (YearMonthNumber) is right justified whereas the MonthOfYear is left justified. Not sure this is significant. I have displayed the value of '$(#=Only([YearMonthNumber])-1' and it looks fine.
I am using QlikView 11.00.11282.0 SR1 64bit. Many thanks in advance.
