3 Replies Latest reply: May 7, 2015 1:51 AM by Shivendoo Kumar RSS

    How to Sort based on Measure Expression in Pivot Table?

    Shivendoo Kumar

      Hi All,

       

      I have 4 Measures and more than 10 Dimensions in my Comparative Analysis Dashboard.

       

      I have below expression for my Measures:

       

      Measures1 Expression =Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Employee Count] )

       

      Measures2 Expression=Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Employee Salary])

       

      Measures3 Expression=Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Company Profit])

       

      Measures4 Expression=Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Deposit Amount])

       

       

      I want to sort my records based on measure selected in Pivot table. How may I achieve this?

        • Re: How to Sort based on Measure Expression in Pivot Table?
          Ankita Agarwal

          Hey,

           

          What do you mean by measure selected in pivot table?

          • Re: How to Sort based on Measure Expression in Pivot Table?
            Michael Solomovich

            Pivot table by its nature is always sorted by dimensions - by 1st dimension, and within each 1st dimension value by 2nd dimension, and so on.

            The best you can do is to sort first dimension by the expression value.  That is, sort it by expression, and enter expression as is there.

              • Re: How to Sort based on Measure Expression in Pivot Table?
                Shivendoo Kumar

                I created a Variable vSort=

                =IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Employee Count|')=1,     

                'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>} [Employee Count] )'    

                ,IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Employee Salary|')=1,     

                'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Employee Salary])'    

                ,IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Company Profit|')=1,     

                'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Company Profit])'    

                ,IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Deposit Amount|')=1,    

                'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Deposit Amount])'

                ,1 ))))

                 

                and used expression =$(vSort) in Expression under tab Sort of Chart and Table.

                 

                It is working perfectly.