I have a dataset structured somewhat like this and I am trying to index the values on a scale from 0 to 100, keeping the scale always the same regardless of how many filters I apply when I try to analyze a timeseries chart composed of:
- Week x Value1
I tried getting the MAX value to join it at a later stage but that fails to come at the same level each time when I set a filter or more, and the indexed values fail to show 100 as top value where the max value should be (sometimes I get 80, sometimes I get 240, depending on how many times a given dimension gets repeated).
| Week | Client | Country | Owner | Business | Value1 |
|---|---|---|---|---|---|
| 1 | A | US | John | B2B | 7232 |
| 1 | A | GB | John | B2B | 6435 |
| 1 | A | PL | Hellen | B2B | 5231 |
| 1 | A | PT | Hellen | B2B | 3345 |
| 2 | A | US | John | B2B | 1234 |
| 2 | A | GB | John | B2B | 2345 |
| 2 | A | PL | Hellen | B2B | 4123 |
| 2 | A | PT | Hellen | B2B | 5423 |
| 3 | A | US | John | B2B | 2345 |
| 3 | A | GB | John | B2B | 7864 |
| 3 | A | PL | Hellen | B2B | 7564 |
| 3 | A | PT | Hellen | B2B | 2345 |
| 1 | B | US | Brad | H&G | 7654 |
| 1 | B | GB | Brad | H&G | 2345 |
| 1 | B | PL | Brad | H&G | 7652 |
| 1 | B | PT | Brad | H&G | 2345 |
| 2 | B | US | Brad | H&G | 6452 |
| 2 | B | GB | Brad | H&G | 4563 |
| 2 | B | GR | Brad | H&G | 1235 |
| 2 | B | FR | Brad | H&G | 7658 |
| 3 | B | US | Brad | H&G | 3452 |
| 3 | B | GB | Brad | H&G | 12341 |
| 3 | B | GR | Brad | H&G | 5346 |
| 3 | B | FR | Brad | H&G | 2341 |
The current output is the following: Unfiltered and unindexed | Week | Wk1 | Wk2 | Wk3 | | ValueTotal | 42239 | 33033 | 43598 |
Filtered by Business "B2B" and Country "US" and unindexed | Week | Wk1 | Wk2 | Wk3 | | ValueTotal | 7232 | 1234 | 2345 |
In the visualization tool, the intended behaviour is the following: Unfiltered and indexed
| Week | Wk1 | Wk2 | Wk3 | | ValueIndex | 97 | 76 | 100 |
Filtered by Business "B2B" and Country "US" and indexed
| Week | Wk1 | Wk2 | Wk3 | | ValueIndex | 100 | 17 | 32 |
So no matter how many filters I apply, all the values have to be relative to the MAX of the current filters
Thanks!