4 Replies Latest reply: Jun 28, 2011 6:04 PM by Leonard Short RSS

    sum(last year's sales)

      Hi, I'm kind of new to qlikview and please forgive me for cross-posting - This is a better spot for this:


      I have Sales data by month/year.  To create an annualized sum(sales) for the current year (regardless of user's selections) I created this expression:    



      =12*(Sum({1<Year = {"=Year(Today())"}>} Sales) / (Month(Today())))

      That works fine.


      But now I want to get last year's total sales (again, regardless of current selections).  So, I figure this oughta work:



      =Sum({1<Year = {"=(Year(Today())-1)"}>} Sales)


      But that yields the Current YTD total.



      Thanks for any hints.  -E.

        • sum(last year's sales)

          And now, after fiddling around and eventually returning to this expression


          =Sum({1<Year = {"=(Year(Today())-1)"}>} Sales)


          is yielding 0.


          I'm getting a different answer with the exact same code.  Oh well.  I don't know.


          I guess the question is changed then,  How do I retrieve sum(last year's sales)?

            • sum(last year's sales)
              Leonard Short

              I use =sum({<Year={$(=max(Year)-1)}>} Sales) to get the prior year's sales.

                • Re: sum(last year's sales)

                  thanks.  that does work.  not eactly what I was looking for though.  I'd like to find what last year's total was regardless of current selections. Today, Last Year would be 2010.


                  But now, this isn't working anymore:



                  =12*(Sum({1<Year = {"=Year(Today())"}>} Sales) / (Month(Today))


                  I didn't do anything to it. it just stopped working correctly.  It is giving me a total based on the current Year selected. 


                  I guess I'll switch to a straight table and hide the column if the Selected Year != the current year.


                  thanks again.