Hello,
I am looking for a way to automatically calculate the highlighted numbers in the example dataset below. The WTD for a particular year is equal to the number in the same Value column when WTD Condition is "x". The MTD for a particular year is the sum of number in Value when MTD Condition is "x". Is there a way to calculate this using the multi-row field tool? Thank you in advance!
Year | Week | Value | WTD Condition | MTD Condition |
2023 | week 1 | 74 | x | |
2023 | week 2 | 92 | x | x |
2023 | week 3 | 99 | ||
2023 | week 4 | 50 | ||
2023 | WTD | 92 | ||
2023 | MTD | 166 | ||
2024 | week 1 | 6 | x | |
2024 | week 2 | 29 | x | x |
2024 | week 3 | 85 | ||
2024 | week 4 | 49 | ||
2024 | WTD | 29 | ||
2024 | MTD | 35 | ||
2025 | week 1 | 80 | x | |
2025 | week 2 | 87 | x | x |
2025 | week 3 | 5 | ||
2025 | week 4 | 27 | ||
2025 | WTD | 87 | ||
2025 | MTD | 167 |
@tsong57
Maybe we can try a bit more dynamic way as below.
I alway like the tool "Transpose" and "Cross Tab", which are powerful for multi-column data processing.
User | Count |
---|---|
18 | |
14 | |
10 | |
6 | |
6 |