January 24, 2018 at 12:06 pm
Hello, I have an issue with one tab cube that doesn't sort out a dates in proper order, yet I have another cube that uses the same date dimensions that does. I'm not doing anything special to either one like an order by in the SQL of the view used by the cube. I'd like to get the errant cube to sort the dates properly.
Example, in the cube that isn't quite sorting right, I have two years of data, 2016, 2017, and 2018, and the date values are MM-DD-YYYY. So using Excel connected to the cube, as you scroll through, you'll see it mixed year order like 12/27/2017 then next row 12/27/2016. The other good date cube, no mixing order by date.
Any ideas of how I can tweak the model design to get the aggregated data in the cube to sort the dates out correctly for PBI and Excel users?
Thanks
January 24, 2018 at 12:41 pm
quinn.jay - Wednesday, January 24, 2018 12:06 PMHello, I have an issue with one tab cube that doesn't sort out a dates in proper order, yet I have another cube that uses the same date dimensions that does. I'm not doing anything special to either one like an order by in the SQL of the view used by the cube. I'd like to get the errant cube to sort the dates properly.Example, in the cube that isn't quite sorting right, I have two years of data, 2016, 2017, and 2018, and the date values are MM-DD-YYYY. So using Excel connected to the cube, as you scroll through, you'll see it mixed year order like 12/27/2017 then next row 12/27/2016. The other good date cube, no mixing order by date.
Any ideas of how I can tweak the model design to get the aggregated data in the cube to sort the dates out correctly for PBI and Excel users?
Thanks
To add a little more detail, though I am using the same date dim viewes to both cubes, with one sorting out dates just fine, and the other doesn't..., would somehow the date column in the cube not sorting properly is getting the date cast or changed somehow to a text column, and that's why? I'm looking to see if this is the case and not finding that.
January 24, 2018 at 1:33 pm
Depending on whether you're using MD or Tabular, there is a sort order or order by column property. Use this to control the sort order and make another attribute for this. It should be yyyymmdd. You may already have a column like Date Key that can be used. This must be 1:1 with the column to be sorted though.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply