5 Replies Latest reply on Dec 6, 2016 9:03 AM by Shinichiro Murakami

    Show values of filtered dimesion

    Philip Perrin

      Hi there -

      I'm working on a solution that shows the aggregated values of those selected in a filter compared to those not in the filter. In the attached workbook, the goal at this point is to have a line on the bottom chart to show the percent of sales that are unselected in the filter (this would allow the user to compare groups of regions. Not that my actual application will have several more than the four regions listed.

       

      I'm thinking a set is necessary to compare the in/out - but with using the filter as a control, it excludes the out entirely. Or perhaps an exclude LOD? Any suggestions?

       

      Thanks,

      Phil

        • 1. Re: Show values of filtered dimesion
          Shinichiro Murakami

          Not sure this logic works or not, anyways.

          You already have enough fields to calculate in/out with LOD, then I just added one more calculated field for amount and percentage respectively.

           

          [Percent of Total (UN-Selected Regions)]

          (sum([{SUM(Small Sales)} ])-Sum([{SUM(Small Sales-Region)}])) / SUM([{SUM(Sales)} (Whole) (Region)])

          BTW, just for curious, is version 10.1 already released?

           

          Thanks,

          Shin

          • 2. Re: Show values of filtered dimesion
            Philip Perrin

            Thanks Shin -

            I appreciate the response! Unfortunately this doesn't quite fit what I'm looking for.

             

            In the screenshot you posted, you had unselected "East." The desired outcome would be a measure (Percent of Total (UN-Selected Regions)) that displays the percent of small sales of "East." Likewise, if "East" and "South" were unselected, then the measure would aggregate the percent of small sales within those two regions and display the overall percent of small sales from those two regions.

             

            The point is to allow me to compare three key measures: overall small sales as a percent of all sales (black line), selected regions' small sales as a percent of all sales in those regions (orange line), and the unselected regions' small sales as a percent of all sales in those unselected regions (red line).

             

            I'll keep tinkering with the formulas as I'm sure there is a way to get at this.

             

            As for 10.1 - Looks like it was initially released on 11/1, with an update on 11/6. I believe I manually went to the customer portal and updated while at TC16.

             

            Again - thanks for looking into this!

            Phil

            • 3. Re: Show values of filtered dimesion
              Shinichiro Murakami

              Hi Philip

               

              I understand your request and revised the file.

              You can simplify some of fields which you don't need to use LOD.

               

              [Order Date (month)]

              date(datetrunc('month',[Order Date]))

               

              [SM_ALL R Small]

              {fixed[Order Date (month)] :sum([Sales of only Small Sales])}

               

              [SM_ALL R Small / Whole]

              attr({fixed[Order Date (month)] :sum([Sales of only Small Sales])})

              /

              attr({fixed[Order Date (month)] :sum([Sales])})

               

              [SM_Selected Small]    // you don't need to use LOD, because it's already filtered.

              Sum([Sales of only Small Sales])

               

              [SM_Selected Small / Whole]

              Sum([Sales of only Small Sales])/sum([Sales])

               

              [SM_UN-Selected Small]  //  whole (LOD) minus selected( filtered)

              attr({fixed[Order Date (month)] :sum([Sales of only Small Sales])})

              -Sum([Sales of only Small Sales])

               

              [SM_UN-Selected Small / Whole]

              (attr({fixed[Order Date (month)] :sum([Sales of only Small Sales])})

              -Sum([Sales of only Small Sales]))

              /

              (attr({fixed [Order Date (month)]:sum([Sales])})

              -Sum([Sales]))

              Change filter to context to apply filters before LOD.

              Thanks,

              Shin

              1 of 1 people found this helpful
              • 4. Re: Show values of filtered dimesion
                Philip Perrin

                Thanks Shin -

                Not only is this a correct and helpful answer - but it also helped me gain a better understanding of how to make these calculations.

                Thanks!!

                Phil

                • 5. Re: Show values of filtered dimesion
                  Shinichiro Murakami

                  You are very welcome and that's the way this forum works!

                   

                  Enjoy!

                   

                  Thanks,

                  Shin