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.
Solved! Go to Solution.
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
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"))
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