2 Replies Latest reply on Feb 26, 2016 3:34 AM by Mark Fraser

How To Calulate If Sales Are Tending Upwards

Looking for the formula to tell me if an items sales are trending upwards or downwards based on last 7, 14 and 30 days sales. If trending upward id like to say trending upward and vis versa.  See Example

7 Day Velocity    14 Day Velocity 30 day Velocity

8.                          6.                      4

• 1. Re: How To Calulate If Sales Are Tending Upwards

Hi Ryan,

I have got a confussion how are you trying to determin the trend first of all. In the example quoted above i can see the values keep on decreasing with increasing time, and the vis versa can be it keeps increasing with time but how are you looking to handle the ups and down trend meaning if numbers are like 8,10 and 6 how should they be treated.

For the above example you can use an IF statement with AND or OR operator which should help you specify your case and get results accordingly.

-Amanjot

• 2. Re: How To Calulate If Sales Are Tending Upwards

HI Ryan

Are you looking for help to bucket the sales into 7, 14 and 30 days?

Or is that done, and you want help with the trend logic? or both?

For the bucketing, I would use DATEDIFF (would it be from today? or a selected date?)

Something like...

IF DATEDIFF('day',[date],TODAY() >=0 and DATEDIFF('day',[date],TODAY() <8 THEN [Sales] ELSE NULL END

(and replicate and expand for the other periods)

That gives you 3 figures, 7, 14, and 30 day sales.

Then the trend... IF will be your friend.

IF 14 > 7 and 14 < 21 THEN 'UP'

ELSEIF 7 > 14 and 14 > 21 THEN 'DOWN'

AND on...

You can use combination of IF with AND/OR to get the desired outcome.

Hope that gives you a start!

Cheers

Mark