Parse Error Formula IF AND

[danniemme]

I feel like basic math is made more difficult in Alteryx but perhaps it is just me!

I have a sales by month and year for 4 years. I want to manually pull some sales out of one month ($2565.57 from month 7, 2019) and spread them across several other months all within the same year ($427.595/month to months 1-6, 2019.)

I tried to write an if statement with +/- the actual #s and it was not having it as it was reading the #s as a string. So I did a text input with these numbers and unioned it with my original data set so there's just a column with all my #s for the basic math. The text input data comes through as a double. My sales are a fixed decimal.

My formula is to add a new column "Sales edited" (which will replace my sales after):

IF [Year]='2019' and [Month]='P07' THEN [Sum_Net Sales]-[Reduce P7] ELSEIF [Year]='2019' THEN [Sum_Net Sales]+[Spread P1-P6] ELSE [Sum_Net Sales] ENDIF

I tried to change the text input data to fixed decimal, no luck. I tried to wrap my formula in TONUMBER(), no luck. Data output is fixed decimal.

[JordyMicheal]

Can you just post a sample of the spreadsheet to work off?

Would be happy to help

[DiganP]

@danniemme Yeah an example of before/after would be very helpful!

[danniemme]

added an attachment to original post, included a tab with expected output.

[JordyMicheal · Accepted solution]

Your formula works just fine, I think you are trying to use Reduce as a command which is an excel function.....

I added Reduce P7 and Spread P1-P6 from a formula to be constant in the workflow. Changed everything to fixed decimal (19.2) but that isn't needed. Then ran your formula as shown below:

IF [Year]=2019 and ([Month]='P07')
THEN [Sum_Net Sales]-[Reduce P7]
ELSEIF [Year] =2019
THEN [Sum_Net Sales]+[Spread P1-P6]
ELSE [Sum_Net Sales] ENDIF

Seems to work just fine, and there is now a line for the Reduce and Spread across every line.
Give it a try 🙂

[danniemme]

thank you!! i didn't catch that i needed to change the year and class to fixed decimal

[JordyMicheal]

@danniemme just for clarification, you don't.

You can leave everything as a certain data type as long as it's numbers.

You don't need to put numbers in quotes though, that treats them as strings