6 Replies Latest reply on Apr 3, 2018 4:16 AM by Peter Harangozo

    aggregate filtered data

    Peter Harangozo


      I wonder if it is possible to create aggregations based on filtering. I tried to create an easy example, hopefully it is not too complicated.

      I have a data structure like this:

       

      user

      filtering parameter1filtering paramterer2value
      user1AX2
      user1AY3
      user2BX4
      user2BY5
      user2AX6

       

      I want to create a multiple choice filtering with sum aggregations:

      If only A and X selected, I want to get a result of 2 for user1 and 6 for user2. It is easy, but here comes the trick:

       

      example1:

      filter1 to A and B and filter2 to X only, I want to get a result of user1 -> 2 (result of the first row) but for user2 -> 10 which is coming as 4+6 as a sum of 3rd and 5th row.

       

      example2:

      filter1 to A, filter2 to X and Y, user1 -> 5 (2+3 regarding the first and second row) and user2 -> 6 (only 5th row's sum)

       

      Is it possible to do somehow?

        • 1. Re: aggregate filtered data
          Eric Hammond

          Hi Peter,

           

          This filtering can be done without parameters:

          • Drag [User] to the Rows shelf. 
          • Drag [Value] to the Text tile on the Marks shelf.  If the aggregation doesn't default to SUM, then right-click to fix.
          • Right-click on the field associated with filter1 (values include A, B, etc.), and choose "show filter".
          • Right-click on the field associated with filter2 (values include X, Y, etc.) and choose "show filter".

           

          Now any combination of the multi-select filters can be used and the sum of Value updates accordingly.

          2 of 2 people found this helpful
          • 2. Re: aggregate filtered data
            Peter Harangozo

            Hi Eric,

             

            Yep it works. Sadly I made a mistake, oversimplified the problem. So I have 1000s of users and I just want to see the distributions of the summarized value.

            • 3. Re: aggregate filtered data
              Eric Hammond

              Hi Peter,

               

              What would "distributions of the summarized value" look like given the sample data above?  I'd like to try again but I need help to understand what you need.

              • 4. Re: aggregate filtered data
                Peter Harangozo

                so after applying the sum you suggested, I want to count the frequency of the values, where on the X axis we can see the sums and the Y is the frequency.

                 

                "example1:

                filter1 to A and B and filter2 to X only, I want to get a result of user1 -> 2 (result of the first row) but for user2 -> 10 which is coming as 4+6 as a sum of 3rd and 5th row."

                 

                that case I want to see a frequency of 1 at 2 and 10, everywhere else 0.

                 

                "example2:

                filter1 to A, filter2 to X and Y, user1 -> 5 (2+3 regarding the first and second row) and user2 -> 6 (only 5th row's sum)"

                 

                that case frequency of 1 at 5 and 6 and everywhere else 0.

                 

                The easiest way would be If I could use the table which appears when I am applying your method and click on the view data option. Is it possible somehow?

                • 5. Re: aggregate filtered data
                  Eric Hammond

                  Hi Peter,

                   

                  I added a few of rows of sample data so that some scenarios have a frequency that is greater than one. 

                   

                  The distribution as described can be accomplished with the help of a "level-of-detail" (LOD) calculation.  It sums the values at the user level so that the number of user IDs for each total value can then be counted.  Try this:

                  • Create a calculated field, [UserTotal]: {FIXED [User]: SUM([Value])}
                  • In the Measures section of data pane, right-click on "UserTotal" and click "Convert to  Dimension".  The reason is that after the values are summed for each user, we no longer want to do additional math on them, but want to treat the total as a category.  This change moves the calc to the diminsions section.
                  • In the Dimensions section of the data pane, right-click on "UserTotal" and click "convert to continuous", because when we chart it we want for the marks to be distributed according to value, not bunched next to each other.
                  • Drag [UserTotal] from the Dimensions section of the data pane to the Columns shelf.
                  • Created a calculated field for [Frequency]: COUNTD([User])
                  • Drag [Frequency] to the Rows shelf.
                  • On the marks shelf, use the drop-down to change from automatic to bars, or circles, depending on what you want to see.
                  • Right-click on [P1] (a field name I've given to the field associated with filter 1) in the dimensions section of the data pane and click "show filter".
                  • Right-click on the new pill for P1 in the Filters shelf, and click "Add to context".  The pill will turn grey.  This is related to [UserTotal], the LOD calculation.  With this filter added to context the LOD calc will apply the filter before determining the total for each user.  If the filter is removed from context, then the total is determined before filters are applied.  In this case, adding the filters to context achieves the desired result.
                  • Repeat for [P2] (show filter; add to context).

                  1 of 1 people found this helpful
                  • 6. Re: aggregate filtered data
                    Peter Harangozo

                    thanks, it works