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
        • 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




            Hope it helps

            Joe Oppelt please validate;)




            • 3. Re: Summarize Multiple Dimensions
              Tim Pickens

              Thank you for trying!