4 Replies Latest reply on Jan 12, 2017 1:30 PM by Karolina Skvortsova

    Last 7 days percent change

    Karolina Skvortsova

      Could you help me to calculate last 7 days percent change?

      I've calculated Last 7 days Sales using this formula IF TODAY() - [Close Date] <= 7 THEN [Sales] ELSE 0 END

       

      But for Last 7 days percent change I've tried to use the following formula, but it doesn't work

      ([Last 7 days]/(IF TODAY() - ([Close Date]) >7 AND TODAY() - ([Close Date]) <= 14 THEN [Sales] ELSE 0 END)

      Could you help me to calculate last 7 days percent change?

      I mean how sales changed for the last 7 days as a percentage

      Screen Shot 2017-01-13 at 00.12.05.png

        • 1. Re: Last 7 days percent change
          Andrew Watson

          Try this, using your formulae, which I'm assuming work:

           

          SUM([Last 7 days])/SUM(IF TODAY() - ([Close Date]) >7 AND TODAY() - ([Close Date]) <= 14 THEN [Sales] ELSE 0 END)

           

          You should be summing the last 7 days and the 7 days prior to that before doing the divide calculation.

          • 2. Re: Last 7 days percent change
            Andrew Watson

            Note the 'ELSE 0' is unnecessary in your formulae - without that everything false would become NULL, which evaluates to 0 when summing the way it has been used.

            • 3. Re: Last 7 days percent change
              Jamieson Christian

              Karolina,

               

              The problem is that your calculations are all happening at the level of individual rows. You need to be able to aggregate and then compute.

               

              One approach is LOD calculations (fixed as Country in this example, based on the screenshot of what you want to do):

               

              [Previous 7 Days Sales]

              { FIXED [Country] : SUM( IF TODAY() - [Close Date] > 7 AND TODAY() - [Close Date] <= 14 THEN [Sales] ELSE 0 END ) }

               

              You'll have to bring this onto your view as a MAX() most likely, as it will get computed for every row and SUM() will blow it out of proportion.

               

               

              Another approach is table calculations, where you group your sales into 7-day blocks and then bring them all onto the view (but perhaps hide all but the most recent 7 days).

               

              [7 Day Time Block]

              INT ( (TODAY() - [Close Date]) / 7 )

               

              Make this a Dimension and put it on your columns shelf, which will yield column headings of (right to left) 0, -1, -2, etc.

               

              Then bring SUM[Sales] on with a "% difference" table calculation based on Table (Across) to compare with the 7-day block to the left.

               

              Finally, hide all but the "0" column to show just the most recent 7-day block. The table calculation will continue to work, even though the other columns are hidden.

              • 4. Re: Last 7 days percent change
                Karolina Skvortsova

                Thank you so much!!! It works!!!