Problem With Prior N Months Expression Using Only and Autonumber

Tue, 12 Jun 2012 13:28:58 GMT

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.

Bredon

topic Problem With Prior N Months Expression Using Only and Autonumber in QlikView

Problem With Prior N Months Expression Using Only and Autonumber