I recently posted an issue I was having with missing data, and received a solution for it. However, I am having a similar issue and am unable to resolve it.
The original issue is documented in this post:
The issue I am having now is similar, but not the same. Originally, I was having a problem with a restaurant that has missing current week sales, but sales exists for the prior 4 weeks, and I received a solution for that. Now, the issue is with a restaurant that has current week sales, but is missing sales for the prior 4 weeks. I tried to implement the solution for the original problem, but I am not able to get it to work in this case.
Here is an illustration of the problem:
| Restaurant | Sales | Trailing 4 Weeks | Growth %
(Incorrect) | Sales for Calc (Correct) | Trailing 4 Weeks for Calc (Correct) | Growth %
(Correct) |
| 1 | 500 | 549.25 | -9.0% | 500 | 549.25 | -9.0% |
| 2 | 0 | 615.25 | N/A | 0 | 0 | N/A |
| 3 | 546 | 480.25 | 13.7% | 546 | 480.25 | 13.7% |
| 4 | 845 | 698.25 | 21.0% | 845 | 698.25 | 21.0% |
| 5 | 555 | 0 | N/A | 0 | 0 | N/A |
| Total | 611.50 | 585.75 | 4.4% | 630.333333 | 575.92 | 9.4% |
In the above example, the original issue was with Restaurant 2. I have a similar issue with Restaurant 5. Both of these restaurants should not be included in the Total calculation. The correct Growth % is 9.4%, not the 4.4% that is being calculated currently.
I have attached the original app updated with the Restaurant 5 example. Can someone help provide me with how I can calculate the total Growth % without including Restaurants 2 and 5?
