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!

Dev Space

Customize and extend the power of Alteryx with SDKs, APIs, custom tools, and more.
SOLVED

ifError formula simplified

AS
8 - Asteroid

Hi Guys,

 

Help me in solving this

=IFERROR([@[Last order date in MEA ID]]-[@[First order date in MEA ID]],"NA")

AY2=31-05-2021

=IFERROR($AY$2-[@[First order date in MEA ID]],"NA")

 

Tried first one but getting error help me please

 

Thanks in advance

 

11 REPLIES 11
atcodedog05
22 - Nova
22 - Nova

Hi @AS 

 

Can you provide sample data and expected output.

BetterFerret
8 - Asteroid

If the variables you are using are dates you can't simply subtract.  You need to use a datetime function as in

IIF(IsNull(DateTimeDiff([Last order date in MEA ID],[First order date in MEA ID],'days')),
'NA',
DateTimeDiff([Last order date in MEA ID],[First order date in MEA ID],'days'))

 

Also you have the Data type of your new field set to Date, when the result of the formula is a string.

Hope this helps

AS
8 - Asteroid

Difference column - =IFERROR($C$1-[@[First order]],"NA")

Difference 2- =IFERROR([@[Last order]]-[@[First order]],"NA")

atcodedog05
22 - Nova
22 - Nova

Hi @AS 

 

Is $C$1 a fixed date or changing date?

BetterFerret
8 - Asteroid

I'm sorry AS, I thought this was an Alteryx question.  Are you looking for the solution in Excel?

AS
8 - Asteroid

This date can change later. Excel user can change it any time.so we need to pickup the date in that same specified

AS
8 - Asteroid

this is an alteyx question....I'm looking the excel query solution in Alteryx..

your solution helped but I'm confused in second formula also

atcodedog05
22 - Nova
22 - Nova

Hi @AS 

 

Here is how you can do it.

Workflow:

 

atcodedog05_1-1628265139763.png

 

 

1. (1st container) $C$1 is a date out of table which needs to be extracted separately. 

2. (2nd conatiner) reading the data reading from 2nd row.

3. Using adding append tool to bring in $C$1.

4. Then calculate the formula.

 

Hope this helps : )

 

BetterFerret
8 - Asteroid

Or this