3 Replies Latest reply on Dec 18, 2013 7:33 AM by Prasanna Kovalam

    Simple (?) Aggregation

    Prasanna Kovalam



      I have a table wherein I have a unique ID, a Geographic region it belongs to (identified by a number) and a status of the completion of each of the unique ID.


      What I want to do is determine the total number of regions which are determined to be complete. This is determined by the following:


      If and only if all the deals in a particular region have a Completion status of 1.00 (100%), only then is the region complete. In my dummy data (shown below), we can see 3 out of the 5 regions fit this constraint. How would I look to make a calculated field to show the value 3?




      My calculated field is called "Completed Regions". I tried to do a COUNT(IF AVG([Completion Status]) = 1 then [Region] end) but this throws up a "cannot mix aggregate and non-aggregate values" error.


      Any help would be greatly appreciated.



        • 1. Re: Simple (?) Aggregation
          Matt Lutton

          I took a stab at this and don't know how helpful it will be, as I'm not sure what you plan to do with this value after you calculate it.


          I created an IF Avg==1 calc: IF AVG([Completion Status])==1 then attr([Region]) end


          Then, for count of completions, I used  IF FIRST()==0 THEN WINDOW_COUNT([IF avg == 1]) END with a Compute Using set to "Region".  In the final "Value to Show" view, region is placed on the level of detail so it can be used in the Compute Using.


          The IF FIRST()==0 part ensures only one value is returned across your regions.  I also turned of stack marks from the "Analysis" >> "Stack Marks" option


          I hope this helps.  I created this in version 8.1, so let me know if you don't have access to it and I'll try to create a Version 8.0 example..

          • 2. Re: Re: Simple (?) Aggregation
            Matt Lutton

            Here is the 8.0 version just in case.  Also, both calcs could be combined into one, I just separated them out to clarify the process I was trying to use.


            That would look like:

            IF FIRST()==0 THEN WINDOW_COUNT(IF AVG([Completion Status])==1 then attr([Region]) END) END

            • 3. Re: Simple (?) Aggregation
              Prasanna Kovalam
              Hi Matthew,  I'm using 8.0 and so I downloaded the 2nd file. However, I can't seem to find the changes you've suggested?