Replicating excel formula in Alteryx

Hi Team,

hope you are well.

I need help. I need to replicate the excel formula in alteryx.

Please find attached the input and output.

The calculation for

1) Avg column- Average of all the three months (But has to be dynamic) for eg. in future if i need to calculate for 2 months then it has be average of last 2 months.

2) Threshold - (IF([@[Average Full Access %]]>10%,"ABOVE THRESHOLD","WITHIN THRESHOLD"))

3) Trend - IF(OR([@[May Count Full Access %]]<10%,[@[June Count Full Access %]]<10%),"RECOVERING AND WITHIN TOLERANCE","INCREASING UPPER LEVELS OF TOLERANCE") - Here the month has to be dynamic because in future it will be based on 2, 3 or 4 or 6 months.

Please let me know how to go about it,

thanks

input_new_V1.xlsx

output_new_V1.xlsx

Datasets

Expression

Accepted answers

sriniprad08

Hi @DawnDuong ,

Thank you so much for helping with this. Really appreciate your time on weekend. I will go through it and let you know about this,

Cheers,

Sri

atcodedog05

Happy to help : ) @sriniprad08

If my response helps please don't forget to mark it as solution.

Cheers and have a nice day!

All comments

DawnDuong

hi @sriniprad08

For Excel formula, as for as I know, Alteryx can replicate any Excel formula... Have you checked out all the interactive lessons which are designed for Excel users? https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Excel%20Users
As for the dynamic inputs, it sounds like you should check out the interface https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Creating%20Analytic%20Apps

Highly recommend that you have a look there first and then figure out which ones you already know how to do vs. those that require a bit more help from the community.

Dawn.

sriniprad08

Hi @DawnDuong ,

Thank you . I could do the first 2 of the requirement. But the third one is not clear to me and i need help on.

sriniprad08

Hi @DawnDuong ,

Please find attached the workflow. i need help on executing the below part.

Thanks for the help,

I'm sorry, I still cannot fully understand what the requirement here is.

Are you able to describe what the use case is e.g. the objective is to find out if "...".

Alternatively, can you show what the EXPECTED results look like and the logic?

Dawn.

sriniprad08

Hi @DawnDuong ,

Thank you so much for helping with this. Really appreciate your time on weekend. I will go through it and let you know about this,

Cheers,

Sri

DawnDuong

Actually I do browse the community more often when I'm on vacation / have more free time and enjoy solving those practical questions which I can more identify with. Cheers.

sriniprad08

@DawnDuong Great to hear about the passion. I have one question on the same problem. Currently we are entering the formula manually for e.g. Please find attached if (Base_month_plus_1) or (Base_month_plus_2).... So in future there will be many months and we have enter manually. Is there a way we can make this dynamic like Alteryx can pick up or give an options to choose number of Base months to compare? Please let me know your thoughts,

Thanks,

Sri

DawnDuong

Hi @sriniprad08

to make sure I understand the ask correctly.
in the current workflow, regardless of the first months (eg July, April, May, Jan etc… ) the data is always compared for the three months. So the “dynamic” starting month is already effective.

do you mean to vary the number of months to be compared?
dawn

sriniprad08

Hi @DawnDuong ,

Thank you for the kind reply. Yes correct. Currently it's kind of manual like below example from the work flow.

Current situation - [Base_month_plus_1]<3 and [Base_month_plus_2]<3 and [Base_month_plus_3]<3 and [Base_month_plus_4]<10 and [Base_month_plus_5]<3

so if any addition of month i have to write something like below manually. adding [Base_month_plus_6]<3

[Base_month_plus_1]<3 and [Base_month_plus_2]<3 and [Base_month_plus_3]<3 and [Base_month_plus_4]<10 and [Base_month_plus_5]<3 <[Base_month_plus_6]<3

and can it be dynamic please like i can select any random month instead of manually adding?

Thanks,

if IsNull([Base_month_plus_1]) or IsNull([Base_month_plus_2]) or IsNull([Base_month_plus_3]) or IsNull([Base_month_plus_4]) or IsNull([Base_month_plus_5]) then "There are not enough data"
elseif [Base_month_plus_1]<3 and [Base_month_plus_2]<3 and [Base_month_plus_3]<3 and [Base_month_plus_4]<10 and [Base_month_plus_5]<3 then "RECOVERING AND WITHIN TOLERANCE"
else "INCREASING UPPER LEVELS OF TOLERANCE"
endif