Hi I have a column with monthly periods repeating from Jan-21 to Dec-21 (Month-Year) and I would like to sort the rows so that the periods repeat from Jan-21 to Dec-21. For Example, below is my source and target table:
Source:
Period | Value1 | Value22 |
21-Jan | Dummy1 | Dummy6 |
21-Jan | Dummy3 | Dummy1 |
21-Jan | Dummy3 | Dummy4 |
21-Feb | Dummy2 | Dummy7 |
21-Feb | Dummy1 | Dummy5 |
21-Feb | Dummy1 | Dummy1 |
21-Mar | Dummy3 | Dummy3 |
21-Mar | Dummy5 | Dummy10 |
21-Mar | Dummy5 | Dummy4 |
21-Apr | Dummy4 | Dummy3 |
21-Apr | Dummy6 | Dummy1 |
21-Apr | Dummy2 | Dummy3 |
21-May | Dummy3 | Dummy3 |
21-May | Dummy1 | Dummy9 |
21-May | Dummy3 | Dummy4 |
21-Jun | Dummy4 | Dummy1 |
21-Jun | Dummy5 | Dummy8 |
21-Jun | Dummy4 | Dummy3 |
21-Jul | Dummy3 | Dummy5 |
21-Jul | Dummy3 | Dummy10 |
21-Jul | Dummy1 | Dummy7 |
21-Aug | Dummy1 | Dummy10 |
21-Aug | Dummy4 | Dummy4 |
21-Aug | Dummy5 | Dummy3 |
21-Sep | Dummy5 | Dummy1 |
21-Sep | Dummy3 | Dummy3 |
21-Sep | Dummy10 | Dummy3 |
21-Oct | Dummy6 | Dummy9 |
21-Oct | Dummy4 | Dummy1 |
21-Oct | Dummy1 | Dummy3 |
21-Nov | Dummy1 | Dummy8 |
21-Nov | Dummy3 | Dummy3 |
21-Nov | Dummy6 | Dummy5 |
21-Dec | Dummy5 | Dummy10 |
21-Dec | Dummy1 | Dummy3 |
21-Dec | Dummy1 | Dummy6 |
Target:
Period | Value1 | Value22 |
21-Jan | Dummy1 | Dummy6 |
21-Feb | Dummy3 | Dummy1 |
21-Mar | Dummy3 | Dummy4 |
21-Apr | Dummy2 | Dummy7 |
21-May | Dummy1 | Dummy5 |
21-Jun | Dummy1 | Dummy1 |
21-Jul | Dummy3 | Dummy3 |
21-Aug | Dummy5 | Dummy10 |
21-Sep | Dummy5 | Dummy4 |
21-Oct | Dummy4 | Dummy3 |
21-Nov | Dummy6 | Dummy1 |
21-Dec | Dummy2 | Dummy3 |
21-Jan | Dummy3 | Dummy3 |
21-Feb | Dummy1 | Dummy9 |
21-Mar | Dummy3 | Dummy4 |
21-Apr | Dummy4 | Dummy1 |
21-May | Dummy5 | Dummy8 |
21-Jun | Dummy4 | Dummy3 |
21-Jul | Dummy3 | Dummy5 |
21-Aug | Dummy3 | Dummy10 |
21-Sep | Dummy1 | Dummy7 |
21-Oct | Dummy1 | Dummy10 |
21-Nov | Dummy4 | Dummy4 |
21-Dec | Dummy5 | Dummy3 |
21-Jan | Dummy5 | Dummy1 |
21-Feb | Dummy3 | Dummy3 |
21-Mar | Dummy10 | Dummy3 |
21-Apr | Dummy6 | Dummy9 |
21-May | Dummy4 | Dummy1 |
21-Jun | Dummy1 | Dummy3 |
21-Jul | Dummy1 | Dummy8 |
21-Aug | Dummy3 | Dummy3 |
21-Sep | Dummy6 | Dummy5 |
21-Oct | Dummy5 | Dummy10 |
21-Nov | Dummy1 | Dummy3 |
21-Dec | Dummy1 | Dummy6 |
Could anyone pls help with this?
Thank you!!
Solved! Go to Solution.
Hey @jaiiracha, here's one way you could go about it whereby you:
1) Use a Multi-Row Formula, grouped by period to create a grouped Index
2) Parse the period into an actual date so it can be sorted properly
3) Sort by the Index (ascending) order, followed by the true date (ascending) order
Can't see any obvious sorting of your other [Value1] and [Value22] columns so haven't touched them but happy to take another look if this is important.
Hi @jaiiracha , here's how I would approach this. Bear in mind this doesn't take into account any logic from your Dummy fields
All great solutions! thank you all!!