Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Issue with Running total

ignas
8 - Asteroid

Hello

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

Capture.JPG

 

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?

2 REPLIES 2
NicoleJohnson
ACE Emeritus
ACE Emeritus

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

ignas
8 - Asteroid

Hello @NicoleJohnson,

 

Thanks for the response. I know what you mean. I had this in mind. Just I have many dimensions that I need to append so I was thinking that it should be something easier. For example Excel pivot table has a function to add 0 for missing values which solves the problem completely.

Regards,

 

Ignas

 

 

Labels