2 Replies Latest reply on Dec 7, 2017 7:24 AM by Andy UK

    Versus last month. Versus last week.

    Andy UK

      Hi there

       

      I've got the code to create a table or a graph and compare data for the current month versus the previous month (and to do the same for week).

       

      However, what I want to do is to allow the user to select a month (which may not be the current month in the data set) and to compare this to data for the previous month.

       

      Example of table:

       

      Sales:

            June 2017      July 2017  Difference (%)

            100               110             10%

       

      Can you help?

       

      Many thanks

       

      Andy

        • 1. Re: Versus last month. Versus last week.
          Norbert Maijoor

          Hi Andrew,

           

          Find my approach as reference below and stored in attached workbook version 10.3 located in the original thread.

           

           

          1. Define parameter

           

           

          2. selection:  IF [Month Selection]="Current Month" THEN today() ELSE DATE([Month Selection]) end

           

          3. Current Month: if DATEDIFF('month',[date],[selection])=0 then [value] END

           

          4. Current month -1:if DATEDIFF('month',[date],[selection])=1 then [value] END

           

          5. %diff: (sum([Current month -1])-sum([Current month]))/sum([Current month -1])

           

          Regards,

          Norbert

          • 2. Re: Versus last month. Versus last week.
            Andy UK

            Hi thanks again for this!

             

            Can you help with me a dynamic title query?

             

            I know how to add the week or month into the title (<parameters. etc>) however how can I include the previous week or previous month selected in the title?

             

            Example, I want to show

            “Week W- 1 versus week W”

            Where W is the week selected

             

            And do the same for another sheet based on month

            “Month M- 1 versus Month M”

            Where M is the month selected

             

            Many thanks