Hello,
I've searched far and wide, and would greatly appreciate guidance for a method of smoothing increasing/decreasing data that results in flat lines separated by major increases, similar to a step line (but each line contains multiple points). I'm trying to create a chart to help identify major changes in demand of a product and competitive product as the price gap between the two products changes over time.
The method of smoothing involves checking each demand value (using velocity as a demand metric) to see if it's greater than the previous demand. If so, a weighted average is used to smooth the SPM value. If the next demand value is also greater, the the weighted average is calculated again with the previous two values. Then that smoothed value is used for all three values. The process is repeated for each row, until the next values is less than the previous weighted average value.
The issue arises if a later smoothed value ends up being greater than the previous value, because then you need to go back and calculated a weighted average with the previous value again. I have included an image below for the step-by-step calculations. Column G represents the Previously, this has been done manually, which as you can imagine is just a brutal, time-intensive process.
[/IMG]
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
This results in the step line chart seen in the photo pasted below.
[/IMG]
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
Any tips for simplifying this process would be awesome. Thank you very much advance!
I've searched far and wide, and would greatly appreciate guidance for a method of smoothing increasing/decreasing data that results in flat lines separated by major increases, similar to a step line (but each line contains multiple points). I'm trying to create a chart to help identify major changes in demand of a product and competitive product as the price gap between the two products changes over time.
The method of smoothing involves checking each demand value (using velocity as a demand metric) to see if it's greater than the previous demand. If so, a weighted average is used to smooth the SPM value. If the next demand value is also greater, the the weighted average is calculated again with the previous two values. Then that smoothed value is used for all three values. The process is repeated for each row, until the next values is less than the previous weighted average value.
The issue arises if a later smoothed value ends up being greater than the previous value, because then you need to go back and calculated a weighted average with the previous value again. I have included an image below for the step-by-step calculations. Column G represents the Previously, this has been done manually, which as you can imagine is just a brutal, time-intensive process.
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
This results in the step line chart seen in the photo pasted below.
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
Any tips for simplifying this process would be awesome. Thank you very much advance!
