4 Replies Latest reply on Oct 20, 2016 10:27 AM by scott K

    Problems using Groups - values appear as null or blank

    scott K

      I work with a long list of customer accounts, but only report details on a small subset - the ones i don't care about are grouped together into "Other Accounts".  For simple calculations this solution works well.  For example i can get the Sum of revenue for all Other Accounts.   Great. 

       

      But when i try to combine with a 2nd data source it stops working.  In the attached example i have two data sources. 1)  Accounts and Revenue  2) Accounts and Projects.  In the example, the project info by phase is reported as null for the Other Accounts Group (see pic).   I have tried different methods, including LOD, but am stumped.   Any help greatly appreciated.

       

      Picture1.jpg..

       

      Thanks

      -Scott

        • 1. Re: Problems using Groups - values appear as null or blank
          Corey Turner

          Scott,

           

          This is a known limitation of Data Blending.

           

          Groups in the primary data source: If a non-additive aggregation from the primary data source is in the view, you cannot use a group created in the primary data source. To work around this limitation, convert the group to a calculated field.

           

          For the suggested work around you could create a calculated field that groups your accounts. For example:

           

          IF [Account Name] = 'Account 4' OR [Account Name] = 'Account 5' OR [Account Name] = 'Account 6' THEN

               'Other Accounts'

          ELSE

               [Account Name]

          END

          1 of 1 people found this helpful
          • 2. Re: Problems using Groups - values appear as null or blank
            scott K

            Corey, thanks for the insights and the link!  I will check it out. 

             

            Your solution is perfect for a small number of accounts.  However my "Other Accounts" group has 500+ accounts grouped together. 

             

            Any suggestions?

            • 3. Re: Problems using Groups - values appear as null or blank
              Corey Turner

              Scott,

               

              I'd try to find another field or combination of fields to identify better identify the group rather than just the account name.

               

              If you have no other fields you can use to identify the "Other Accounts", you may be able to use the number somehow. For example if the group consists of account 4, 5, 6+ (so greater than 3) you could pull the number out of the name and use it in a condition. (IF [Account #] > 3 THEN 'Other Accounts' ELSEIF [Account Name] END)

               

              Another idea, is to drop the "Other Accounts" in a set. Right click "Account Name" -> Create Set -> Paste your accounts to be grouped (you may need to click custom value list and paste in the "Enter Text to Add").

              Then create a calculated field that does a set membership test. (IF [Other Accounts Set] THEN 'Others' ELSE 'Account Name' END)

              1 of 1 people found this helpful
              • 4. Re: Problems using Groups - values appear as null or blank
                scott K

                Corey - thanks so much.  That worked.  I created the calculated field with other combinations to to identify the group.  

                 

                All the best.

                Scott