1 Reply Latest reply on Jan 28, 2017 10:39 AM by Jamieson Christian

    increasing versus decreasing calculation on EPA CO2 data

    Erin Williams

      I am trying to do something and have checked a few resources but can't quite seem to make this work.

       

      I have the MM tones of CO2 emitted by state data from 2000-2014 from the EPA.  Literally 3 columns, state name, amount and year. 

       

      What I am trying to calculate is if each state is getting better or worse and grouping the results into 3 buckets.

       

      Ex. 

       

      Massachusetts

      2000 - 82

      2009 - 2014 avg - 67

      % difference from 2000 to last 5 year average (67-82)/82 = -18% (Yay!)

       

      Texas

      2000 - 652

      2009-2014 avg - 605

      % diff  = -7%

       

      What I would like to do is 1, figure out how to do this calculation, and 2, bucketize the results, so say 15% and above are Level 1, 10-14.9% are Level 2 and 9.99% and less are Level 3. 

       

      I'd also like to know if the last 5 years are increasing or decreasing.  Between these two calculations, I want to assign a score to each state. 

       

      Thanks for any help you can offer. 

        • 1. Re: increasing versus decreasing calculation on EPA CO2 data
          Jamieson Christian

          Erin,

           

          First of all, props for using the word "bucketize" in a sentence!

           

          Second of all, you really should attach a packaged workbook so the community can review your progress so far and validate any proposed solution that we provide you. Since this is EPA data, it should be in the public domain, and you should not need to anonymize your workbook before posting it.

           

          Now… without having seen your workbook, I would say that likely the most straightforward solution will be a simple aggregation in conjunction with conditional value materialization. Something like this:

           

          [% Change]

          SUM( IF [Year] >= 2009 AND [Year] <= 2014 THEN [Amount] END ) / SUM( IF [Year] = 2000 THEN [Amount] END ) - 1
          

           

           

          This simple aggregation will compute over whatever level of detail is on your view, so if you put [State] on the Filters or Rows shelf, it will compute it for each state.

           

          Hope that helps. If you have any more questions, I strongly encourage you to post your workbook (as a TWBX packaged workbook) so we can take a closer look at your work.