Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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:

MonthOfYearYearYearMonthYearMonthNumberAmount
620112011-06110
720112011-07210
820112011-08310

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

Community Browser