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.
In the screenshot below my January 2016 and 2017 data is being aggregated together. I want two January columns of different colors for each sepearate year. Sample data included. No way to attach anything here and there are too many rows to display in this forum.
| Change | Summary | Customer | Status | Reported Date |
| CH10000007 | Change | CLOSE | 6/30/16 4:27 AM | |
| CH10000008 | Change,Application,Must Select CI | CLOSE | 7/1/16 6:33 AM | |
| CH10000009 | Change | CLOSE | 7/1/16 9:14 AM | |
| CH10000011 | Change,Hardware-Mainframe | CLOSE | 7/1/16 9:17 AM | |
| CH10000014 | Change,Application,Must Select CI,Password - System ID | CLOSE | 7/5/16 8:40 AM |
Ok, in a table visual Select
in Axis Reported Date-Herarchy--Month
in Legend Reported Date-Herarchy--Year
in Values Count of Change}
Let me know if you need more help
The auto date hierarchy you are encountering automatically aggregates the data based on the level of the hierarchy you are in. So, in a monthly view, you will see months 1-12 with data possibly (and in your case definitely for Jan) being counted from multiple years. The easiest way to change this is to click on the drop down arrow from the Date field in the Visuals area and select Date instead of Date Hierarchy.
However, if you want to see the data at a monthly level but distinguishing years, you will have to follow some of the methods given above.