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

Unknown Variable when performing calculations.

soncoku
9 - Comet

Greetings everyone

 

I'm stuck at Empty Expression.


Table 1 is a google sheet file. It has x columns and a primary key.
Every day to that table is added a column, the weekday with the x data
For example:
Monday (is added on Monday)
Tuesday (is added on Tuesday) and so on.
My problem is that my workflow has a formula that does calculations with all the Weekdays.

Example:
Balance = Budget - Monday - Tuesday - Wednesday - Thursday - Friday - Saturday - Sunday

But today for example, in the google sheet data I don't have the other weekdays except Monday and Tuesday, so I get the error "Unknown Variable" for Thursday.

 

I tried this solution:
I've inserted a Text Input and added all the weekdays.
I want to (Append maybe) these two data streams together so that I have all the weekdays there.
So if I run the calculations I have all the weekdays there.

 

I don't know if my way of thinking was right, because the Text Input I created has all the weekdays, and I don't know if that would cause a problem if that weekday exists in the  google sheet as well. I'm guessing it would have to check if the column exists first, before appending them. Idea would be to append only the columns with the weekdays that don't exist on google sheet yet. So on Sunday it wouldn't need to append anything.

This solution didn't work, I got the error Empty Epxression

 


Right now that formula works only on Sunday, when all weekdays are inserted as columns.
Any idea how to achieve this?
(p.s Creating the weekdays as columns in the google sheet with empty rows is not an option).

2 REPLIES 2
kelsey_kincaid
12 - Quasar

Hi @soncoku ,

I wonder if transposing your data, then summarizing the data, then writing your formula as Budget - Sum_Value would work for this? Let me know if that helps. Happy to keep brainstorming if that doesn't quite accomplish what you're looking for. 

soncoku
9 - Comet

thank you for the reply @kayers

 

I managed to do it by creating a Text Input with the same column names as the other Data Source and performing a union. 

Apparently I needed to perform a IF statement to check all Weekdays if they exist and replace the null values with 0.

 

🙂

Labels