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.