2 Replies Latest reply: Oct 29, 2012 11:18 AM by Jonathan Dienst RSS

    Sorting using an expresion with Set Analysis

      Hi,

       

      I have some data which is stored in by month i.e.

       

      MonthData

      May

      100
      Apr150
      Aug200
      Jul20
      Jun50

      Dec

      80
      Oct120
      Jan130
      Mar125
      Sep180
      Nov160
      Feb90

       

      Because month is a text field i have created an inline table with the following:

       

      DateIDData
      1Apr
      2May
      3Jun
      4Jul
      5Aug
      6Sep
      7Oct
      8Nov
      9Dec
      10Jan
      11Feb
      12Mar

       

      I have created a graph using set anylsis so that when I select the a date the graph still displays all the data. I have also created a sorted exted epression (=DateID) so that I can order the months in the correct order.

       

      The problem is that is I have no mont seleceted everything is fine, but when I select the date the order changes on the graph. The data is all there it just changes the order and I cant work out why.

       

      Any help would be greatly appreciated

       

      Cheers

       

      Simon

        • Re: Sorting using an expresion with Set Analysis
          Stefan Wühl

          Try

           

          =only({1} DateID)

           

          as sort expression (or even better, use a dual as dimension value (like create your Month using month() function and sort by numeric value).

          • Re: Sorting using an expresion with Set Analysis
            Jonathan Dienst

            Simon

             

            I assume that the set expression in your chart includes the month (Data) field. This is causing it to ignore the month selection (or possibly use the month selection to create a range of more than one month), but the sort table is now limited by the selection.

             

            A better approach is to use a proper date field for the month. I your case, your fiscal year starts in April (I assume), so it might be easier to use a manually created dual. Something like this:

             

            LOAD

                 Dual(Date, DateID) As Date

            INLINE

            [

                 Date, DateID

                 Apr, 1

                 May, 2

                 ...  (etc for Jun - Feb) ...

                 Mar, 12

            ];

             

            There are more elegant ways, using a master calendar (search the forum for more information), but this is a quick & dirty way that should work. Now you just sort by Date using a numerical sort.

             

            Hope that helps

            Jonathan