Perform calculations with "Unknown variables"

[soncoku]

Greetings alteryx community.

I've settup this workflow where I do calculations of the earnings every week.

The data source is a google sheet.

It works fine for the past week that the data exist.

So far so good.

Now let's say that today is TUESDAY evening.

If I try to calculate the earnings of this week I'd get an error, and it's normal because the google sheet only contains the weekdays MONDAY and TUESDAY so far.

And I get the error:

Error: Formula (77): Parse Error at char(35): Unknown variable "Wednesday" (Expression #1) 

The error is very logical because the other variables don't exist yet : 

[PAID INCOME-ALL INCOME]-[Monday]-[Tuesday]-[Wednesday]-[Thursday]-[Friday]-[Saturday]-[Sunday]

Is there a way to make to make it ignore the variables that don't exist yet and make them = 0 or null , in order to let the calculation perform.

Because this way I can only see the earnings at the end of the week when all weekdays are created on google sheet.

But I want to be able to do the calculation any day of the week.

Any help is apreciated.

[BenMoss · Accepted solution]

Hi,

There's a couple of different routes you could go, the first one is to transpose all of the columns that you wish to total into a single field, before using the summerize tool to aggregate them (the transpose tool has the option to also transpose 'unknown' fields.

Another option is to use a text input, simply specifying the headers of all the fields you expect, you can then union this against your dataset prior to the formula tool, and Alteryx will generate those fields with null values as a result.

Ben

[soncoku]

Great advice. 

I created a text filed and assigned the headers "Monday Tuesday Wednesday Thursday Friday Saturday Sunday," and that took care of the "Unknown Variable " error.

Then I used Union.

Now I get "Empty expression" error, which is normal as they are empty.

How do I managed to populate the empty expressions with null value?

I'm guessing it should be something like:

IF(IsEmpty([Monday]) then [Monday] = Null())
IF(IsEmpty([Tuesday]) then [Tuesday],Null())
IF(IsEmpty([Wednesday]) then [Wednesday],Null())
IF(IsEmpty([Thursday]) then [Thursday],Null())
IF(IsEmpty([Friday]) then [Friday],Null())
IF(IsEmpty([Saturday]) then [Saturday],Null())
IF(IsEmpty([Sunday]) then [Sunday],Null())

I could use data clensing tool to populate it with "0" but I want null insted.

Could you please also guide me to form the right if statement for each date.

Thanks in advance.

You already helped me a lot.