Dev Space

Customize & extend the power of Alteryx. 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
20 - Arcturus

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
20 - Arcturus

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
20 - Arcturus

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