Issue with Running total

Question

Hello

I try to estimate running total, but I run into the problem.

As you can see from the picture there is no data for Country1 for 2017 02. That means there is no Running total for this month even though Running total for Country1 for 2017 02 month should be 2 as it is a sum of first two months.

How can I add a full list of Countries for every month?

It is a very simplified version of my data as I have a lot of missing rows.

Does anybody have an idea how to solve this simple problem?

NicoleJohnson · Accepted Answer

Try the attached (created in version 11.3):

1. Summarize both countries & dates, then append the list of dates onto the list of countries

2. Join back to the original data

3. Union the unmatched Country-Date combos to the joined data to capture months with no data for each country

4. Sort/clean-up data & calculate your running totals

Is that what you were hoping to achieve? NOTE: If you don't have at least 1 country with a record for each date you wish to include, this might not give you a complete set of data... for example, if no countries had data for 2017 04, but they did for every other month of the year, then your final data would have a "missing month" where there was no activity for any of the countries... but I'm guessing this isn't going to be the case with your particular project. If it might be an issue, let us know! Could maybe come up with something using generate rows/etc.

Cheers!

NJ