alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Replicating excel formula in Alteryx

11 - Bolide

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.

thanks

27 REPLIES 27
13 - Pulsar

1. 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%20Us...
2. 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%2...

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.

11 - Bolide

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.

11 - Bolide

Hi @DawnDuong ,

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

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.

Thanks for the help,

Sr

13 - Pulsar

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

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.

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.

11 - Bolide

Hi @DawnDuong ,

I am sorry if not clear. Please find attached the input and the output.

The objective is the get the Column "E" Trend in the attached output file.

Please let me know if its still not clear.

Thanks,

Sr

13 - Pulsar

I kind of understand what you need. In this case, given that the "base month" is different for each combination of ADS-RGF, you can first find the "base" month and then include the base month as one of the field.

This way, you can easily have the month offset +0/+1/+2 etc... and hence do not run into the issue of having messy column and it is also easier to detect the combinations where there are not enough data point (at least 3 months?).

Below is my suggestion, with attached workflow. Feel free to edit and adopt as required for the use case you have in mind.

11 - Bolide

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

13 - Pulsar

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.

11 - Bolide

@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

Labels