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

    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:





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


      load * inline [

      Year, MonthOfYear, YearMonth







      load * Inline [

      YearMonth, Amount





      The attached dashboard shows that the YearMonthNumber is being generated as I anticipated.  Output from table:




      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.