Populating a formula with unknown data

Hi all. I'm new to Alteryx so hopefully this is simple.

I am trying to create a formula in my workflow to total weekly costs. There are up to 10 different cost elements that might be recorded during the period, but the difficulty is that if a cost is not incurred that week it is not present in the data. For example, if Cost Type 2 is not incurred, the data doesn't list it as 0, it just skips from Type 1 to Type 3 as shown below.

Since a particular cost type may not be listed in a given week my formula shows an Unknown Variable error.

Is there any way to keep all fields but have Alteryx fill the non-included variables with 0?

Thanks!

Help

Accepted answers

JosephSerpis

Hi @johneodell I mocked up an example using your sample data showcasing how to answer your question. Like the others who have posted in this question there are many ways to answer your question. It's one of the great features of Alteryx there are so many ways to answer a question. In the example workflow attached I've commented and annotated as much as possible to explain how I solved the question.

In regards to tagging some in a response that is based on your community ranking this link explains in more detail but essentially you need to have the rank of Meteoroid to have that ability.

Total_271019.yxzp

All comments

JosephSerpis

Hi @johneodell I mocked up a workflow that I think answers your question. Let me know what you think?

Total_241019.yxmd

johneodell

Thank you! This gave me some ideas, but I don't think I was as clear in my original description as I should have been. We are getting a new data set every week, so the week 1 data would be a separate file from the week 2 data. The result is that in some weeks our source has 5 cost types, other weeks it may only have 3 or 4. This is the part I'm having difficulty with.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703