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:

       

      [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