Forum Discussion
Problem in getting % change values
This has been one harrowing tale where so far I have tried every trick in the book to get the answer but nothing has worked so far. I want something like column F( which uses created column G). The ...
Would you be able to provide the expected result and how it is calculated? You can paste the table visual into Excel and create formulas that show the calculation logic.
| Business Unit | Ordered Item | Qty | Qty Fulfilled | Schedule Ship Wk | % change |
| ASC | WP-9938 | 39 | 39 | 1 | 0 |
| FRAM | BWP2118SP | 298 | 298 | 1 | 0 |
| FRAM | BWP2157BR | 34 | 34 | 1 | 0 |
| ASC | WP-373 | 198 | 198 | 2 | 941% |
| ASC | WP-490HD | 32 | 32 | 2 | 941% |
| ASC | WP-592 | 40 | 40 | 2 | 941% |
| ASC | WP-601 | 601 | 601 | 2 | 941% |
| ASC | WP-657 | 411 | 411 | 2 | 941% |
| ASC | WP-715HD | 328 | 328 | 2 | 941% |
| ASC | WP-775 | 859 | 859 | 2 | 941% |
| ASC | WP-853 | 416 | 416 | 2 | 941% |
| ASC | WP-9100 | 90 | 90 | 2 | 941% |
| ASC | WP-9240 | 704 | 704 | 2 | 941% |
| ASC | WP-9829 | 22 | 22 | 2 | 941% |
| ASC | WP-9861 | 44 | 44 | 2 | 941% |
| ASC | WP-HD6301 | 98 | 98 | 2 | 941% |
| FRAM | BWP2056BR | 1 | 1 | 2 | 941% |
| FRAM | BWP2056GP | 4 | 4 | 2 | 941% |
| FRAM | BWP2095GP | 3 | 3 | 2 | 941% |
| FRAM | BWP2115BR | 2 | 2 | 2 | 941% |
| FRAM | BWP2195BR | 2 | 2 | 2 | 941% |
| FRAM | BWP2422BR | 6 | 6 | 2 | 941% |
| ASC | WP-1106 | 75 | 75 | 3 | 282% |
| ASC | WP-1983 | 406 | 406 | 3 | 282% |
| ASC | WP-2067 | 107 | 107 | 3 | 282% |
| ASC | WP-2093 | 35 | 35 | 3 | 282% |
| ASC | WP-2221 | 267 | 267 | 3 | 282% |
| ASC | WP-2271 | 406 | 406 | 3 | 282% |
| ASC | WP-2378 | 154 | 154 | 3 | 282% |
| ASC | WP-2468 | 600 | 600 | 3 | 282% |
| ASC | WP-2684 | 117 | 117 | 3 | 282% |
| ASC | WP-366HDA | 84 | 84 | 3 | 282% |
| ASC | WP-373HDP | 18 | 18 | 3 | 282% |
| ASC | WP-413HDA | 6 | 6 | 3 | 282% |
| ASC | WP-595 | 140 | 140 | 3 | 282% |
| ASC | WP-645 | 189 | 189 | 3 | 282% |
| ASC | WP-661 | 140 | 140 | 3 | 282% |
| ASC | WP-726 | 660 | 660 | 3 | 282% |
| ASC | WP-836 | 160 | 160 | 3 | 282% |
| ASC | WP-853 | 336 | 336 | 3 | 282% |
| ASC | WP-888 | 94 | 94 | 3 | 282% |
| ASC | WP-9046 | 450 | 450 | 3 | 282% |
| ASC | WP-9164 | 172 | 172 | 3 | 282% |
| ASC | WP-9225 | 437 | 437 | 3 | 282% |
| ASC | WP-9361 | 1320 | 1320 | 3 | 282% |
| ASC | WP-9408 | 216 | 216 | 3 | 282% |
| ASC | WP-9414 | 490 | 490 | 3 | 282% |
| ASC | WP-9839 | 72 | 72 | 3 | 282% |
| ASC | WP-9860-EA | 402 | 402 | 3 | 282% |
| ASC | WP-9933 | 40 | 40 | 3 | 282% |
| ASC | WP-9939 | 5 | 5 | 3 | 282% |
| ASC | WP-HD6073 | 140 | 140 | 3 | 282% |
| ASC | WP-TM27K6105 | 27 | 27 | 3 | 282% |
| Carter | M60318B-0101 | 188 | 188 | 3 | 282% |
| FRAM | BWP2556BR | 9 | 9 | 3 | 282% |
| FRAM | BWP511SP | 2 | 2 | 3 | 282% |
| FRAM | BWP9240DG | 43 | 43 | 3 | 282% |
| FRAM | WP462 SP | 20 | 20 | 3 | 282% |
| FRAM | WP635 BLANCA | 1 | 1 | 3 | 282% |
| FRAM | WP635 SP | 55 | 55 | 3 | 282% |
% change =abs( sum of order quantitiy for a particular week- average of order quantity for all previous weeks)/average of order quantity for all previous weeks
Your code works well for outer filters, but when I add fields inside the table it starts giving weird results. I have 15 more fields that I want users to slice/dice the data on. This % change data will only depend on the week selected.