Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Replicating excel formula in Alteryx

sriniprad08
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.

 

Please let me know how to go about it,

 

thanks

27 REPLIES 27
DawnDuong
13 - Pulsar
13 - Pulsar

hi @sriniprad08 

 

  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.

sriniprad08
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.

 

 

sriniprad08
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

DawnDuong
13 - Pulsar
13 - Pulsar

hi @sriniprad08 

 

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.

 

sriniprad08
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.

sriniprad08_0-1634360245447.png

 

Please let me know if its still not clear.

 

Thanks,

Sr

DawnDuong
13 - Pulsar
13 - Pulsar

hi @sriniprad08 

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.

forSriNi.PNG

sriniprad08
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

DawnDuong
13 - Pulsar
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.

sriniprad08
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

sriniprad08_0-1635240611657.png

 

Labels