Excel to Alteryx Formulae

[allwynbazil]

Team,

Please help me to convert this Excel formula to the Alteryx formula.

=IF([Start Date]>Quote!$B$6, IF([No of days]>1094, ([Extend Price]*0.12), ""), IF([New End Date]-[Today]>1094, ([AnnList_Price]*([New End Date]-[Today]/365)*0.12), ""))

Regards,

Alwin

[ShankerV]

Hi @allwynbazil 

I see the formula can be used directly as below.

If Quote relates to same row, use the Formula tool

IF([Start Date]>[Quote], IF([No of days]>1094, ([Extend Price]*0.12), ""), IF([New End Date]-[Today]>1094, ([AnnList_Price]*([New End Date]-[Today]/365)*0.12), ""))

If Quote relates to another row like Row-1, use the Multifield formula tool.

IF([Start Date]>[Row-1: Quote], IF([No of days]>1094, ([Extend Price]*0.12), ""), IF([New End Date]-[Today]>1094, ([AnnList_Price]*([New End Date]-[Today]/365)*0.12), ""))

Many thanks

Shanker V

[binuacs · Accepted solution]

@allwynbazil  if the field Quote!$B$6 is constant you can add it directly into the formula.

=IIF([Start Date]> Quote!$B$6, IIF([No of days]>1094, ([Extend Price]*0.12), ""), IIF([New End Date]-[Today]>1094, ([AnnList_Price]*([New End Date]-[Today]/365)*0.12), ""))

[allwynbazil]

@binuacs by mistake, I accepted as a solution😀. But definitely I would have done it later.

 Sorry I dint get the formula. Can we convert into Alteryx If formula? Quote B6 is nothing but [Today]. Appreciate your help.

regards,

Alwin

[binuacs]

@allwynbazil  you can remove the mark from the solution, Alteryx should work with IF then Else End and IIF() formula, I used the second one

the given formula should work. Can you try

=IIF([Start Date]> [ToDay], IIF([No of days]>1094, ([Extend Price]*0.12), ""), IIF([New End Date]-[Today]>1094, ([AnnList_Price]*([New End Date]-[Today]/365)*0.12), ""))

[allwynbazil]

Thanks @ShankerV . Can we convert my formula to Alteryx formula as applying the same formula is not working?

Regards,

Alwin

[binuacs · Accepted solution]

@allwynbazil Just noticed that you are using the date fields, you need to use DateTimeDiff() function to calculate the difference in days

=IIF([Start Date]> [ToDay], IIF([No of days]>1094, ([Extend Price]*0.12), ""), IIF(DateTimeDiff([New End Date],[Today]) >1094, ([AnnList_Price]*([DateTimeDiff(New End Date],[Today])/365)*0.12), ""))

[ShankerV · Accepted solution]

Hi @allwynbazil 

Please use the below formula..

IF([Start Date]>[Today] AND [No of days]>1094)
THEN [Extend Price]*0.12
ELSEIF([New End Date]-[Today])>1094
THEN ([AnnList_Price]*([New End Date]-[Today]/365)*0.12)
ELSE ""
ENDIF

[allwynbazil]

I think "=" not required before IIF in the formula. Correct me if am wrong.

[ShankerV]

Hi @allwynbazil 

Yes, = is not required here in Alteryx