Converting Excel Formula to Alteryx Language

[quims]

Hi Alteryx Community,

I have another question about formula as I'm planning to migrate my working file to Alteryx but have some difficulties in converting these formula to Alteryx.

My current formula is as follow. See attached file.

Formula 1: =IF(SUMPRODUCT(($A$2:$A$99997=A2)*($E$2:$E$99997))<B2,"15",IF(SUMPRODUCT(($A$2:$A$99997=A2)*($F$2:$F$99997))<B2,"45",IF(SUMPRODUCT(($A$2:$A$99997=A2)*($G$2:$G$99997))<B2,"75")))

Formula 2: =IF(SUMPRODUCT(($A$2:$A$99998=A2)*($E$2:$E$99998))<B2,"15",IF(SUMPRODUCT(($A$2:$A$99998=A2)*($F$2:$F$99998))<B2,"45",IF(SUMPRODUCT(($A$2:$A$99998=B2)*($G$2:$G$99998))<B2,"75")))

Thanks in advance.

[RolandSchubert]

Hi @quims ,

it works a little bit differently, but of course you can "translate" to Alteryx. In general, you can calculate the sum using the Summarize tool,
join the sum to the original data and use a Formula tool to apply the conditions. You use two different formulas with different row ranges
in the arrays, so you'l have to filter the rows to use. 

I've attached a sample workflow, hope, this is helpful.

Best,

Roland

[quims]

wow! this is great! thank you, @RolandSchubert 

btw, in connection with the file that i share. would you also help me on this - i will create formula between a string and a date. when i use a formula it can calculate since in the conditions are string and date are involved.

My formula: 

=IF(E2="Cr",(I2-C2),IF(E2="Dr",(I2-D2),"CHECK DR & CR"))

[RolandSchubert · Accepted solution]

There is a function available to subtract days from a specific date. As the result field may also contain a string (Check CR/DR), a bit of conversion is needed. See attached workflow,hope it returns the result you expect.

[quims]

Thank you, @RolandSchubert 

both methods you've been suggested are working fine! thanks again for the help! 🙂

btw, can a regex be the solution when i add to a specific line if criteria is met. sample, if column a is equal to Cr then [Type] will be adding a suffix word [Type] "Credits".

-quims