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

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