0 Replies Latest reply: Jun 12, 2012 9:28 AM by bredon99

# 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

[Date]:

Year, MonthOfYear, YearMonth,  autonumber(YearMonth) as YearMonthNumber;

Year, MonthOfYear, YearMonth

2011,6,'2011-06'

2011,7,'2011-07'

2011,8,'2011-08'

];

[Sales]:

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.

Bredon