I would like to make a calculated field which is a fixed percentage of itself, aggregated by category AND respect the others filters of date, company and product.
date | company | category | product | value |
---|---|---|---|---|
2022-01 | AA | A | p1 | 10 |
2022-01 | AA | A | p4 | 10 |
2022-01 | AA | B | p1 | 3 |
2022-01 | AA | C | p2 | 23 |
2022-01 | BB | A | p1 | 6 |
2022-01 | BB | A | p4 | 7 |
2022-01 | BB | B | p1 | 11 |
2022-01 | BB | C | p2 | 21 |
2022-02 | AA | A | p1 | 1 |
2022-02 | AA | B | p1 | 5 |
2022-02 | AA | C | p2 | 16 |
2022-02 | BB | A | p1 | 5 |
2022-02 | BB | B | p1 | 9 |
I created the calculated fields:
- value by PRE_FILTER = sumOver(value, [date], PRE_FILTER)
- value by PRE_AGG = sumOver(value, [date], PRE_AGG)
For example, when I filter company = “AA” in January 2022, value by PRE_AGG gives me the expected answer of 46. In February also gives me the expected answer of 22. I would like to fix these totals whenever I filter the category at the same time it is dynamicaly filtered by the others filters of date, company and product.