Hi Community,
I have the following sample data:
| ID | Term | Value | Country |
| A | 1Y | 1 | UK |
| A | 3M | 2 | UK |
| A | 2Y | 3 | UK |
| A | 2M | 4 | UK |
| A | 3Y | 5 | UK |
| B | 1Y | 6 | FR |
| B | 3M | 7 | FR |
| B | 2M | 8 | FR |
| B | 3Y | 9 | FR |
I use a "Cross Tab" to create columns from the values found in column "Term" (note that 1Y is 1 year, 2M is 2 months, etc). I populate the columns with the values from column "Value". Thus I end up with:
| ID | Country | 1Y | 2M | 2Y | 3M | 3Y |
| B | FR | 6 | 8 | | 7 | 9 |
| A | UK | 1 | 4 | 3 | 2 | 5 |
I would like the table to show the columns in the following order:
| ID | 2M | 3M | 1Y | 2Y | 3Y | Country |
| B | 8 | 7 | 6 | | 9 | FR |
| A | 4 | 2 | 1 | 3 | 5 | UK |
Thus the "Term" columns are ordered from shortest (2 month) to longest (3 year). And column "Country" is the last column. I can use a "Select" to set the order of the columns of course. But is there a way to do this dynamically? Thus if my data suddenly contains terms "1M" or "5Y" or whatever, it automatically allows for these new terms.
I can use a formula to map the terms to months: for example "2M" maps to 2, "3M" maps to 3, "1Y" maps to 12, ... and so on. But then what?
Thanks.
