Sometimes when you are using MONTH and YEAR date attributes, BIME might not sort the dates properly. When we added the MONTH and YEAR attributes in that order, our results ended up looking something like this:
The solution
With the help of a couple calculated measures and a sorting option, we can change the order of our results.
To sort dates by Month and Year:
- In the Calculation Engine menu, click Standard Calculated Measure.
- Name your measure Date Timestamp.
- Type in DATE_TO_TIMESTAMP.
- Select a DATE attribute from your Fields drop-down list.
- Add your calculated measure as a tooltip with the AVG aggregator.
- Go back to the Calculation Engine menu, then select Standard Calculated attribute.
- Type in the following formula: [MONTH(your added month)+ " "+[YEAR(your added month)]
- Add your attribute to Columns.
- In the Advanced sort options, sort your query based on your newly created measure.
Your query will now display results in the correct order!
