12 Replies Latest reply on Feb 11, 2013 5:58 AM by Jonathan Drummey

    Sort Descending Customization

    Jason Back

      Hello all,

       

      I have a data with multiple columns and rows and I would like set one fix column in descending order.  The colums are divided by date.

       

      The example is attached.  What I'd like to do is have growth rate desceding for 2013.  I can simply use descending botton on the toolbar but when I change the location by filter it does not stay the same for other locations. I would like to have it always fixed for 2013.  Would there be a way.

       

      Any help would be much apprecaited. Thank you.

      Jason

        • 1. Re: Sort Descending Customization
          Mark Jackson

          What you are wanting to do is sort on a table calculation, which isn't possible. There is an idea for this that you can vote on:

          Sort on Partition Calculations

          • 2. Re: Sort Descending Customization
            Andrew Watson

            Could you create a calculated field just containing 2013 growth rate to use as a sorting column? Then you could either:

             

            1. Sort the dimension using that field - click on the pill and select sort by field

            2. Add the sorting field to the rows shelf and unselect 'Show Header'.

             

            From the worksheet published it's hard to work out what you're sorting by hence I can't attach a workbook with the solution, but one of the 2 options above should work for you.

             

            Andrew

            1 of 1 people found this helpful
            • 3. Re: Sort Descending Customization
              Mark Jackson

              Andrew - The growth rate is a table calculation, which can't be used in the sort dialog. Otherwise, your approach would work. I think the only way to get this to work would be to manually create a calculation for each of the years you want displayed. Your column header would then be "Measure Names". That way you could calculate the growth rate value without using table calculations, since you are not partitioning the information.

              • 4. Re: Sort Descending Customization
                Mark Jackson

                This is the best I can give you. It has limitations though, which is why I recommend voting on the idea above.

                1 of 1 people found this helpful
                • 5. Re: Sort Descending Customization
                  Joshua Milligan

                  Jason,

                   

                  I think I've got a solution that avoids those limitations.  While it is true that you cannot sort by a table calc, there is an implicit sort (lowest to highest).  So if you put the table calc field as a discrete value as the first row header, all rows will be sorted by that.  If you want a descending sort, just multiply by negative one.

                   

                  The complication your requirement adds is that you wanted it to be specific to a given year.  So, the solution gets slightly more complex.  I've attached a workbook so you can see.  It uses your year parameter to dynamically sort by the growth rate for the selected year.

                   

                  The basic outline of the approach is:

                  1. Calculate the minimum year for each row in the table.

                  2. Use that year and the selected year (2013 in your example) to determine the offset for a lookup calculation.  Specifically you are looking up the value for 2013.

                  3. Cleanup the field (multiply by -1 for sort descending and account for nulls).

                  4. Add that field to the rows as a discrete value.  The table is now sorted.

                  5. Hide the row headers for that field (since it really is meaningless, except for sorting, after step 3)

                   

                  If you have any questions, I'd be happy to answer!

                   

                  Joshua

                   

                  Message was edited by: Joshua Milligan to clarify a few items.

                  1 of 1 people found this helpful
                  • 6. Re: Sort Descending Customization
                    Jason Back

                    Thank you so much Joshua, it's a bit complicated for me but after reading the fuctions I get the concept.

                     

                    One question - so I'd like to remove the lins between the bubbles.  I can remove the row divider to get rid of them but the bubble sare still seperated by the rows.  Do you know if there is a way to allow bubbles to overlap in visuals just like the initial example I've attacehd?

                     

                    Thanks again.

                    Jason

                    • 7. Re: Sort Descending Customization
                      Joshua Milligan

                      Jason,

                       

                      You can remove the lines by formatting the table and removing the row dividers.  You can also increase the spacing so the bubbles don't get chopped off.  I'll have to give it a little more thought to see if there is a way to allow for the overlap present in the original.

                       

                      I've attached a new workbook to show the formatting.

                       

                      Joshua

                      • 8. Re: Sort Descending Customization
                        Mark Jackson

                        I think the overlap is affected by having multiple items in the row shelf. You can make the label for 2006 be the item name. Then you can move it to level of detail. It isn't perfect, but it works.

                         

                        BTW, brilliant solution Joshua. I'll be using that one. I still wish you could use the sort dialog so that the it wasn't necessary to go to such depths.

                        • 9. Re: Sort Descending Customization
                          Joshua Milligan

                          Mark,

                           

                          Thanks!  I absolutely agree -- it would fantastic if calculated fields were included in the sort dialog.  I'm guessing the reason they are not is that whatever is selected in that sort dialog is going to be passed to the underlying database as an order by clause.  But the table calculations are happening after the data is returned to the data engine.  I'm sure some logic could probably be put in place to determine where the sort should happen based on which field was selected but I don't know how easy it would be to implement in the current architecture or what implications there would be for performance.  Maybe version 9?  I think I'll vote up that idea.

                           

                          Joshua

                          • 10. Re: Sort Descending Customization
                            Jonathan Drummey

                            @Joshua - calculated fields that are regular aggregates are included in the sort dialog, it's only table calcs that are not.

                             

                            I saw in Sheet 4 in the attached that it was hiding 2006, I prefer using a table calc filter for that because then the worksheet doesn't have to be updated when there is new data. I set that up in Sheet 6 in the attached.

                             

                            Jonathan

                            • 11. Re: Sort Descending Customization
                              Mark Jackson

                              Jonathan - Are there advantages for using "!=" as opposed to "<>"? Or is it just preference?

                              • 12. Re: Sort Descending Customization
                                Jonathan Drummey

                                I learned programming using !=, so that's what I tend to use.