2 Replies Latest reply on Aug 15, 2017 2:23 AM by nagaraju.mallavalli

    Sorting on a Calculated Filed

    nagaraju.mallavalli

      Hi All,

       

      • I have a scenario that has last 6 months on Columns, States on Rows and Sales on Text, now i need to show the difference between current month sales(Ex: as on Today - Aug-2017) and previous month sales (July-2017 in this case) as a new filed(let's name it as Variance) that should appear just right to Current month sales.
      • I need to have the capability to sort my view based on this Variance filed and/or on my Current month sales in either Ascending or Descending order depending on my choice

       

      I am using 10.3 version at present, and uploading a sample workbook, kindly take a look and give me a solution please........

        • 1. Re: Sorting on a Calculated Filed
          Michel Caissie

          Nagaraju,

           

          You can create two parameters for the sorting selection .

          Next create a calculation [Sorter] like this

          case [Sort Order]

          when 'Asc' then

              case [Sort By]

              when 'Variance' then [Variance]

              when 'Current month sales' then MIN( [State Current month sales] )

              end

          when 'Desc' then

              case [Sort By]

              when 'Variance' then -[Variance]

              when 'Current month sales' then - MIN( [State Current month sales] )

            end

          end

          )

           

          where  State Current month sales is

          {INCLUDE [State]: SUM(if DATETRUNC('month', [Order Date]) = DATETRUNC('month', TODAY()) then Sales end)}

           

          Set [Sorter] as discrete and put it at the left of State on the Rows. You can then unselect Show Header on the pill.

           

          see in the attached

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Sorting on a Calculated Filed
            nagaraju.mallavalli

            Hi Michel,

             

            Thank you very much, the explanation given here is perfectly matched to my requirement ..