Ordering the Months
Currently when I am displaying the MonthName field it defaults to showing me the names in alphabetical order. How do I fix this to show me the regular order (January, February, March, April, May, June, July, August, September, October, November, December)?
4 replies
-
Hi Katherine,
You do not mention if you are using the Pyramid IMDB engine (or other SQL based DB), or connected to some other analytic engine (like SSAS or SAP HANA).
If you have created a Pyramid model against our IMDB engine, or against a standard RDBMS database, then you can specify the order of the Month Name in the model by using the Month Number or Month Start Date as a the sort criteria. This is done automatically if you have used the Time Intelligence Preparation block in your data flow:
If you are connecting to a model where you have no access to edit the model, then you can specify "Custom Order" in Discovery and then select the Months in the order you would like them to appear:
Deselect all the elements for that Month Name attribute.
Right click on the Month Name attribute and select Quick Sort, Custom Sort.
Select the Month Name attributes in the order you wan them to appear.Process the query.
Hope that helps.
Ian
-
Hi, Thank you for the help with that. I was working with an in-memory database. However I do not have access to change or manipulate the data model. I followed your process and was able to sort these on a new discovery.
Another question I had with this was how to organize the MonthName within a filter.
I was trying the same custom sort approach but it did not work. Also I already have two sorts in this data set, that I don't want to mess up.
Hopefully you have some ideas that will help me Ian Macdonald
-
Hi Katherine,
First, I would try to contact whoever created the model and ask them to change it so that the MonthName is ordered correctly. This will save a load of time and effort in future.
In the meantime, the way around this is to create a set, ordered by selection, then use the set in the filter.
Open the Formulate App and select "List", then the right model.
Drag the "Standard Set" block onto the canvas
Drag the "Order by Selection" block from the Sort section onto the canvas
Click on the Standard Set block then choose the MonthName attribute, then select the months in the correct order
Click on the Pyramid Icon to test the set. you should see the months ordered correctly in the bottom right hand pane:
In your Discover View, place the MonthName in the filter Zone, make sure all the standard elements are deselected, then click the "Fx" button to see your calculated members and sets and select the ordered set:
Further to my previous reply, you can of course now use your set in any situation when you need the months ordered correctly:
Hope that helps.
Ian
-
Ian Macdonald I agree with you there, I think whoever set up the data model did not realize what they were doing and not doing.
Thank you for the walk through it worked!!!