Hi there,
I am trying to build a report which requires me to create a calculated dimension, and wondering if one of you guys can help me.
Below are my dataset and desired result. Basically, I would like to create a dimension whose members are ">" and "<=" . Note that ">" correponds to observations in the datasets whose sum of Value 1 is greater than Value 2. Similar idea apply to "<=".
Any help will be greatly appreciated. Thank you.
DataSet:
| Dim_01 | YYYYMM | ID | Value1 | Value2 |
| AA | 201303 | 1 | 1 | 29 |
| AA | 201303 | 2 | 92 | 88 |
| AA | 201305 | 1 | 76 | 37 |
| AA | 201305 | 2 | 2 | 73 |
| AA | 201307 | 1 | 10 | 1 |
| AA | 201307 | 2 | 98 | 7 |
| BB | 201303 | 3 | 58 | 40 |
| BB | 201303 | 4 | 91 | 50 |
| BB | 201305 | 3 | 25 | 10 |
| BB | 201305 | 4 | 45 | 83 |
| BB | 201307 | 3 | 10 | 43 |
| BB | 201307 | 4 | 74 | 5 |
| CC | 201304 | 1 | 40 | 47 |
| CC | 201304 | 2 | 64 | 46 |
| CC | 201306 | 1 | 39 | 9 |
| CC | 201306 | 2 | 62 | 11 |
| CC | 201308 | 1 | 26 | 13 |
| CC | 201308 | 2 | 76 | 6 |
| DD | 201304 | 3 | 51 | 96 |
| DD | 201306 | 3 | 1 | 98 |
| EE | 201304 | 4 | 4 | 20 |
| EE | 201306 | 4 | 1 | 69 |
Desired Result
| Dim_01 | Value1 vs Value2 | Avg. Value |
| AA | > | |
| | <= | |
| BB | > | |
| | <= | |
| CC | > | |
| | <= | |
| DD | > | |
| | <= | |
