2 Replies Latest reply on Jun 17, 2012 1:52 PM by Joe Mako

    Summing aggregated data

    Scott Shaffer

      I have some account level data for which I have created a calculated field that gives the account a 1 or 0 based on some characteristics of the account. Here's the basic field..

       

      if sum((if [Brand]="Industry Solutions" and [Revenue]>0 then 1 else 0 end))>0

      and

      sum((if [Brand]="System x" and [Revenue]>0 then 1 else 0 end))>0 then 1 else 0 end

       

      So, on an account level it works great, it tells me if there's revenue in two brands.

       

      But if I want to look at all the accounts summed up in a certain Region it sums up all the data and gives me a "1" for the region. What I really want is the sum of all the 1's, or the count of accounts with two brands revenue.

       

      Hopefully I would then be able to take that count of accounts with brand revenue and compare it to other calculated fields that count accounts with brand revenue.

       

      Attached a file with a subset of data.

       

      Thanks!