5 Replies Latest reply: Oct 28, 2010 2:01 PM by John Witherspoon RSS

    Sales this Year but not Previous two years

      Hi,

      I have succesfully used this expression to calculate those customers who purchased a certain product this year but not last year. How would I modify this to calculate for sales this year but NOT last year OR the year before? (CUSTABRNO=CUSTOMER)

       

      sum

       

      ( {<CUSTABRNO = p({<Year={$(=max(Year))}>})-p({<Year={$(=max(Year)-1)}>} CUSTABRNO)>}AMOUNT) ;

       





        • Sales this Year but not Previous two years
          John Witherspoon

          I'd guess you could just add one more p() expression to it:

          sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
          -p({<Year={$(=max(Year)-1)}>} CUSTABRNO)
          -p({<Year=($(=max(Year)-2)}>} CUSTABRNO)>} AMOUNT)

          Or list both years in one p() expression:

          sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
          -p({<Year={$(=max(Year)-1),$(=max(Year)-2)}>} CUSTABRNO)>} AMOUNT)

          Or check for a range of years (only really useful if you have more than two):

          sum({<CUSTABRNO=p({<Year={$(=max(Year))}>})
          -p({<Year={"<=$(=max(Year)-1) >=$(=max(Year)-2)"}>} CUSTABRNO)>} AMOUNT)

            • Sales this Year but not Previous two years

              Thanks John, worked out perfectly.

              Another quick question, is there a quick way of setting the year to be Fiscal Year? using some sort of SET function?

              Currently when I click 2010 it takes the Calendar Year. I want my selection of 2010 to start October 1st. 2010.

                • Sales this Year but not Previous two years
                  John Witherspoon

                   


                  Les Backman wrote:Another quick question, is there a quick way of setting the year to be Fiscal Year? using some sort of SET function?
                  Currently when I click 2010 it takes the Calendar Year. I want my selection of 2010 to start October 1st. 2010.


                  I'd just create a Fiscal Year field. For instance:

                  year(YourDate)+(month(YourDate)<10) as "Fiscal Year"

                  Or:

                  date(yearstart(YourDate,0,10),'YYYY') as "Fiscal Year"



              • Sales this Year but not Previous two years
                Thom Mumaw

                Hi Les,

                I am trying to do the same thing I think you are. Have you been able to count the number of customers who purchased an item last year but not this year? I am trying to create a customer retention report and I am looking for the syntax to count customers. (something like your sum).

                Thanks
                Thom

                  • Sales this Year but not Previous two years

                    What I was doing was, my user would select a supplier/product and then the list would generate showing customers who purchased last year but not this year as well as the amount from last year that was not purchased this year.

                    I also used this same formula but with selecting a customer and showing which products they purchased last year but not this year.

                    I have not attempted to do anything with counting the number of customers however i'm sure something could be figured out using what I did and the COUNT() function.