2 Replies Latest reply on Jan 10, 2018 4:47 AM by Andrew Morgan

    Best sales year and month

    Andrew Morgan
    Andrew Morgan Jan 5, 2018 9:55 AM

      Hi Experts,

       

      I want to find name of best year and name of best month in each year based on sales for a given person.   For example, for Anna sales for 2017 is highest so name should be 2017. For Kelly,

      it should be 2016. On the same line I want to find best month in each year (including best year also).

      So my final output should be :

      Person | Best Year Name | Best Month name

       

      Would appreciate your help. v10.4 is attached.

       

      Thanks in advance.

       

      AM

        • 1. Re: Best sales year and month
          Simon Runc
          Simon Runc Ambassador Jan 5, 2018 10:12 AM (in response to Andrew Morgan)

          hi Andrew,

           

          I think this does what you require. I've worked them both up using LoDs, but there are (as always) many ways we could have done this.

           

          For best year I did the following

          [Best Year Per Person]

          IF {FIXED [Person], Year([Order Date]): SUM([Sales])}

          =

          {FIXED [Person]: MAX

          (

          {FIXED [Person], Year([Order Date]): SUM([Sales])}

          )

          }

          THEN Year([Order Date])

          END

           

          and then for best month

          [Best Month Per Person]

          IF {FIXED [Person], month([Order Date]): SUM([Sales])}

          =

          {FIXED [Person]: MAX

          (

          {FIXED [Person], month([Order Date]): SUM([Sales])}

          )

          }

          THEN datename('month',[Order Date])

          END

           

          Hope that solves your question, and makes sense.

          1 of 1 people found this helpful
          • 2. Re: Best sales year and month
            Andrew Morgan
            Andrew Morgan Jan 10, 2018 4:47 AM (in response to Simon Runc)

            Hi Simon,

             

            Thanks for the reply. Best year is working fine but best month is not giving the desired result. I want best month in each year.

            Please help.

             

            AM