2 Replies Latest reply on Mar 24, 2016 12:11 PM by Joe Oppelt

    Calculated field as a Global Filter

    Manjiri Virkar

      Hi,

       

      I am trying to incorporate a functionality where using index() I am selecting top 10 rows from the table, sheet1. I have created a calculated field called index and using that as a filter on Sheet 1, I am selecting top 10 rows. However, I want to display the calculated total in Sheet 2 for the selected 10 rows.

       

      I am unable to apply the index filter (calculated field) on both sheets, How can I achieve this functionality?

      Also, what is the reason behind not able to make an calculated field as global filter?

       

      Please find the attached workbook (v9.0.1)  as an example.

       

      Regards,

      Manjiri

        • 1. Re: Calculated field as a Global Filter
          Manjiri Virkar

          Joe Oppelt: Could you please help me with this one?

          • 2. Re: Calculated field as a Global Filter
            Joe Oppelt

            See attached.

             

            I changed your Top rows Filter to use a parameter.  (Not crucial, but useful to a user.)

             

            For Sheet 3 I created 2 calcs.  One does a simple window_sum.  the other does a window sum using the parameter.  You want the one that uses a parameter.  I put both on there to show the difference.  When you use a table calc for a filter (and INDEX() is a table calc function), then you aren't filtering out rows in the table,  you are filtering out what gets displayed.  That's why the simple table calc comes up with such a huge number.  The good one uses the same table calc logic that the filter does to sum up only the values you want to see.

             

            Using that table calc approach, you need to do the same for all the measures you want to aggregate.

             

            Then, look at sheet 4.  On Sheet 3 you'll notice that the table calc displays the same value for each row.  So on sheet 4 I just want to show one of the rows.  I don't need the Top Rows Filter for this sheet.  Just do Index=1.   Take out the SUM(measure) for each measure and replace it with the table calc.  And if you don't want to see City and segment, etc., then uncheck "Show Header" for those dimensions like you see in sheet 5.