Forum Discussion
Dates improperly aggregating in visualization
A few things you need to fix.
1. My example of using a number (1-12) was an example if you have a Month column. Since you are using Month and Year, you need to make a unique order column for this value. I would make a calc column (query editor) that takes Year*100+Month. This will give you values like 201601 for Jan2016, 201602 for Feb2016 etc. Make sure the data type is numeric and this will allow you to sort correctly across multiple years.
2. You don't need to concantenate this unique sort order column with the Month Year column. Just select the field from the fields list, go to the Modeling tab, and choose Sort By > Unique Sort Order column.
You need to put [MonthYear] as the axis. If you have months with no data, they will not be shown by default, but by clicking on the drop down arrow on the field (in the Visuals pane) you can turn on showing values with no data. To sort string values (text) in a non alphabetical way, you need to make a column that has the sort order. So, if you make a table that has month name Jan...Dec, you would need a column that has 1...12 for the months. Then, if you select the month name field and go to the Modeling tab, you can choose "Sort By" and choose the order column.
Ok I did as you suggested and created a separate column with numbers aligning to the months. However this has created a new issue. I have data going from June (number 6) to December (number 12) and finally January (number 1). If I sort acending or decending I end up with my columns ordered from 12 - 1 or 1-12 wich is incorrect since this data spans 2 years. I need the months to order 6-12 then 1 for January 2017.
Below I have a picture of a decending sort. As you can see it starts at 12 (Dec 2016) and goes backwards to 6 (June 2016) with the last column 1 (January 2017). I think if the data were for only one year your method would work. However still having trouble due to this spanning multiple years. Is there a way to fix this?
A few things you need to fix.
1. My example of using a number (1-12) was an example if you have a Month column. Since you are using Month and Year, you need to make a unique order column for this value. I would make a calc column (query editor) that takes Year*100+Month. This will give you values like 201601 for Jan2016, 201602 for Feb2016 etc. Make sure the data type is numeric and this will allow you to sort correctly across multiple years.
2. You don't need to concantenate this unique sort order column with the Month Year column. Just select the field from the fields list, go to the Modeling tab, and choose Sort By > Unique Sort Order column.
Thank you. Simple math fix that worked great. You were right that I did not need to concantenate however I still had to use the formulas to break out the Month and Year fields to make your solution work. Thank you for the help!