Excel to Alteryx Formulae

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

Expression

Accepted answers

binu_acs

@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), ""))

binu_acs

@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

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

All comments

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

binu_acs

@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

@binu_acs 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

binu_acs

@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

binu_acs

@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), ""))

allwynbazil

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

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335