Populating a formula with unknown data

[johneodell]

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!

[JosephSerpis]

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

[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.

[DanM]

@johneodell,

Typically when I see this type of issue I suggest taking a look at the Transpose tool and see if you can put all your costs into one field and then work your calculations there and then use the Cross tab to put it back. Remember to use a Record ID before the Transpose tool so you can put the data back in the same place.

[johneodell]

With each response I'm learning a little more about Alteryx. I've only been using it for about a week,

Here is perhaps a better example of what the data looks like in it's raw form:

For this particular week, there is no Cost Type 4 in the file, but next week's file may not include Cost Type 5. Here is the desired final result:

Here is what I have in my workflow so far:

I've also included an example of my data. Any help is GREATLY appreciated.

Thanks,

John

[danilang]

Hi @johneodell 

Here's a workflow that demonstrates what @DanM was referring to. 

The area of interest is the bottom branch.  By transposing the data you end up all the values in one name/value column pair.  the summarize function adds up the values without having to reference explicit fields that may or may not be present.  After that, join back to your original data to give 

Shout out to @JosephSerpis for doing all the heavy data prep work in his workflow.

Edit: after seeing your latest post, it looks like your data is already transposed format.  In that case use the Total Column Aggregation method in the Cross tab tool to generate the sum automatically

 

Dan

[johneodell]

Would one of you be kind enough to use the column headers included in my data sample (Transport Costs) in the formulas? It's in one of my previous replies but I'll include it here too. I'm still learning how Alteryx works with regard to conditional statements and sometimes I need things explained to me like I'm a child. Lol

Also, how do you tag someone in a response?

Sorry for being such a pain,

John

[JosephSerpis · Accepted solution]

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. 

[johneodell]

Thank you so much, Joseph! I appreciate your patience. I learned a lot and hopefully my second week of using Alteryx will go more smoothly.