9 Replies Latest reply on Jul 23, 2018 8:34 AM by Jalen Asperger

    Running Total of Count by Year

    Jalen Asperger



      I am trying to create an animated map of the cumulative count of events over time (years), but due to the way the Pages pane/animation works, I can't seem to figure it out. Right now, if I put "Year" in the Pages pane, it shows the number of events and their corresponding location on my map for that year, but I want them to be cumulative. So if I have 6 events in one location in 2009, 9 events in 2010, and 5 events in 2011, I want 6 to show up for 2009 (the first year), 15 for 2010, and 20 for 2011. I think it's a bit difficult because each observation in my dataset is one of these events I'm trying to count, but I'm only counting the ones that meet certain conditions.


      I've been trying to find a way to calculate a running total/cumulative count for each year, but can't seem to figure it out!


      The "show history" checkbox part of the animation pane kind of works in theory, but it shows a dot on my map while I want to be filling each area (census tracts).


      I appreciate the help!

        • 1. Re: Running Total of Count by Year
          Shinichiro Murakami

          Hi Jalen,


          Could you please attach your sample data as twbx format?




          • 2. Re: Running Total of Count by Year
            laura barksdale

            I tried doing it with the example data of 6, 5 then 9 events over one geographic area.  I think you should be able to do this by clicking the dropdown arrow to the right of the pill displaying the count and clicking add table calculation.  Then Make the table calculation a running total of the sum.  Then make sure you compute using specific dimensions so you can select your geographic and Date dimensions.


            • 3. Re: Running Total of Count by Year
              Jalen Asperger

              I'll attach a workbook to this post. I had to strip away all but the essential data due to confidentiality concerns.


              I was able to achieve almost what I want using laura barksdale's suggestion from above, but not quite. It calculates the proper running totals for each census tract, but it still only shows the census tracts with events that occurred that specific year when putting things in motion. So when the last year comes up, I want the final result to look like this:


              Rather than what I'm currently getting:


              • 4. Re: Running Total of Count by Year
                Shinichiro Murakami

                Hi Jaren


                To make this happen, the modification on data connection is needed.

                I am not sure what is your original two sources, but if these are join-able, could you attach two sample files (This is not twbx).




                • 5. Re: Running Total of Count by Year
                  Jalen Asperger

                  There are multiple excel files and a shapefile that I use in my original workbook. I will attach the shapefile zip (that will need to be unzipped) and a sample excel file. These are the only two files necessary to create the maps I have.

                  • 6. Re: Running Total of Count by Year
                    Jalen Asperger

                    The primary issue here is that I need to retain the running sum even when there are no events that year. So the example I gave where there were 6, 9, and 5 events for 2009, 2010, and 2011 would work fine using the running sum table calculation, BUT every census tract has years where there are no events. Because the Pages pane acts as a filter, it's not displaying the data for years where there are no events.


                    If I have a census tract with 3, 0, 2, and 0 events for 2009-2012, it will display 3 in 2009, disappear in 2010, 5 in 2011, and disappear again in 2012.


                    Hopefully that helps explain my problem a little more.

                    • 7. Re: Running Total of Count by Year
                      laura barksdale

                      Hi Jalen

                      so in your demographics excel doc i added a new sheet with just a list of years.

                      I joined this onto the other sheet through a cartesian join.  you can do this by in the join pane scrolling to the bottom of the field options, clicking the calculated field option then entering 1 as the calculated field for both sides.

                      now you can drag the new year field [year1] to pages and the states should stick around through the run. However now the numbers will be wrong because of the duplicates.

                      We know the real data is where year = year one so I mad a calculated field that gives those values a value of 1 and others a value of 0.

                      if [Year] = [Year1]then 1

                      ELSE 0


                      Then we can replace the count of values with the sum of this field.


                      I made my year list long so that it wouldn't have to be updated. To remove those extra dates I also created a field to filter them out.

                      Hope this helps!

                      1 of 1 people found this helpful
                      • 8. Re: Running Total of Count by Year
                        laura barksdale

                        I forgot to say the step where you make it a running calc like before.  Also, it just occurred to me you may not want to see the zip codes until they have their first value, I added a filter using that running sum to exclude values of zero so now the areas appear when they get their first value and you can see the map build.

                        1 of 1 people found this helpful
                        • 9. Re: Running Total of Count by Year
                          Jalen Asperger

                          This solution works perfectly! Thank you so much!


                          The only issue is that it does cause some interference with other calculated fields. Most can be resolved with filtering out the "zero value dupes" but I do still have some where I'm having issues.