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

# Last 7 days percent change

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

• ###### 1. Re: Last 7 days percent change

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

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

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

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