3 Replies Latest reply on Nov 21, 2018 11:57 AM by Tim Pickens

    Summarize Multiple Dimensions

    Tim Pickens

      I've been stuck on this for a while now and my apologies if this has been covered elsewhere.  It likely has been, I just don't know what to search on so any suggestions are appreciated.

       

      I have values in multiple Measures and I'd like to present a high-level view to start with, and then allow a user to dig deeper if needed.  The problem is how to calculate a single value.  I've tried assigning the true/false to 1/0 and then summing by location, and then using an IF/THEN to see if more than one column is true, but somehow keep hitting the error: "Cannot mix aggregate and non-aggregate arguments with this function".

       

      So in the data below, for Location 1: there are multiple measures where the value is true and I want to return something telling the User they need to dig deeper.  For Location 2-3:  there may be multiple rows where a measure is true, but there is only one measure having a true value.

       

      The data source is an Oracle Custom query if that matters and I can't post a workbook.

           

      Thanks again for any advice!

       

      Meas 1Meas 1Meas 2Meas 3Meas 4LocationDesired Output
      Location 1TRUEFALSEFALSEFALSELocation 1Multiple
      Location 1TRUEFALSEFALSEFALSELocation 2Meas 1
      Location 1FALSEFALSETRUEFALSELocation 3Meas 3
      Location 1FALSETRUEFALSETRUE
      Location 2TRUEFALSEFALSEFALSE
      Location 2TRUEFALSEFALSEFALSE
      Location 2TRUEFALSEFALSEFALSE
      Location 2TRUEFALSEFALSETRUE
      Location 3FALSEFALSEFALSEFALSE
      Location 3FALSEFALSEFALSEFALSE
      Location 3FALSEFALSETRUEFALSE
      Location 3FALSEFALSEFALSEFALSE
        • 1. Re: Summarize Multiple Dimensions
          Joe Oppelt

          Once you have an aggregate (of any sort:  SUM, MIN, ATTR, etc.) in a calc, all fields in that calc have to be aggregated.  Many times it's just a matter of wrapping the non-aggregated field in ATTR().

           

          If you can't get it to work, I'll need a workbook to see what's going on.

           

          You can upload a workbook with proprietary data by anonymizing it.  Here is a brief video demonstrating how to do that.  It will take about 10 minutes to do.

           

          Video demonstrates how to anonymize your workbook/data

           

          You'll end up with an excel data source that you swap into your sheet in a copy of your workbook.  That will allow you to strip out proprietary rows, change sensitive data values and field names, etc.  And you end up with a much smaller data source, which is easier to upload and download.

          1 of 1 people found this helpful
          • 2. Re: Summarize Multiple Dimensions
            Norbert Maijoor

            Hi Tim,

             

            Not sure but find my attempt below and stored in attached workbook version 10.2 located in the original thread.

             

             

            I pivoted the the table first

             

            1. D1. Check Pivot Fieldname: if [Pivot Field Values]=TRUE then  [Pivot Field Names] END

             

            2. D2. Display: if {fixed [Meas 1]:min([D1 Check Pivot Fieldname])}={fixed [Meas 1]:max([D1 Check Pivot Fieldname])} then str({fixed [Meas 1]:max([D1 Check Pivot Fieldname])}) else 'Multiple' end

             

            3. Drag the required objects to the indicated location and set filter Pivot Field Values: to TRUE

             

            TRUEFALSE.png

             

            Hope it helps

            Joe Oppelt please validate;)

             

            Regards,

            Norbert

            • 3. Re: Summarize Multiple Dimensions
              Tim Pickens

              Thank you for trying!