4 Replies Latest reply on Mar 14, 2017 11:41 AM by David Roscoe

    Show Filtered Items as Sum Total in Table

    David Roscoe

      I've put a quick packaged workbook together to try and show what I am trying to achieve.

       

      I want to be able to show all values over a set amount (600 in the example workbook) in a table, and anything below this amount as one 'Other' row with a sum total in the costs column. So, any items that are filtered out will display as one 'Other' row. This is so that I can limit the entries shown in the table to material items, yet still tie back the grand total to all of my other reports.

       

      This is a variation on the 'Top N and Other' posts of which there are several in these forums, however these examples use a user defined Top N and can therefore use the Rank function to filter the table. As my data is more dynamic ( the Top N is dependant on how many entries there are over 600) I am struggling to adapt the other forum posts to my situation.

       

      I am wondering if I need to create some sort of count function to determine my 'Top N' parameter rather than having this set by the user, but I can't figure out how to do this.

       

      Thanks,

      Dave R

        • 1. Re: Show Filtered Items as Sum Total in Table
          Michel Caissie

          Dave,

           

          you can use lod to compute the Field2 cost.

          {FIXED [Field2]: SUM([Cost])}

           

          Next you can modify your calculation1 for

          IF [Field2 (cost)] > 600 THEN [Field2] else "Other" END

           

          Using this, you get rid of the Attr() function , and the Calculation1 becomes a dimension. So you can put Calculation1 (copy)  on the Rows  without the need of Field2 on the Detail.You get a single mark for 'Other' instead of 4,  and this mark will get the SUM of only the  'Other' field2 values.

           

          Michel

          1 of 1 people found this helpful
          • 2. Re: Show Filtered Items as Sum Total in Table
            David Roscoe

            Many thanks for your help Michel, I wouldn't have thought to do that.

             

            What you put together worked perfectly, however, my actual data is more complex and I'm having difficulty applying the method.

             

            I have put together another test workbook which mirrors my actual data more closely.

             

            As you can see I have 2 calculated fields which are working out variances across different periods (Variance 1 & 2).

             

            I then have a field named Variance 3 which totals the first two.

             

            As there are positive and negative amounts I have used an 'ABS' field to calculate the absolute value so that I can filter on variances over a certain amount either way (using the standard filter function 'at least 350').

             

            In my workbook the first sheet is unfiltered. The second sheet is what I currently have, which is filtered on amounts over 350 but without the 'Other' field.

             

            The third sheet is my attempt at adding the 'Other' field into the table but as you can see it has not worked as expected.

             

            Am I doing something wrong somewhere with the LOD calc?

             

            Any help is greatly appreciated.

             

            Thanks,

             

            Dave R

            • 3. Re: Show Filtered Items as Sum Total in Table
              Michel Caissie

              Dave,

              You can check on Sheet 4 if you get the expected values.

               

              First, I suggest that you FIXED  the cost  and then making other calculations, instead of making calculations with the cost and then FIXING the results.

              Here in your view, you have two field dimensions , so you have to include both in the lod.

               

              Also, since for each  Field1 / Field2 group, you need the cost for each period, I would go with calculations like

              {FIXED [Field1],[Field2]:SUM( if Period = 'Period1' then [Cost1] end )}

               

              Once, you have your 3 costs, you can compute the Variances

              [Fields(Cost)(Period1)]

              -

              [Fields(Cost)(Period2)]

              1 of 1 people found this helpful
              • 4. Re: Show Filtered Items as Sum Total in Table
                David Roscoe

                This is brilliant, thanks Michel that has worked & is exactly what I needed.

                 

                Thanks for all of your help with this!

                 

                Regards
                Dave