Replicating excel formula in Alteryx

[sriniprad08]

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

[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

[atcodedog05]

Hi @sriniprad08 

Can you provide sample data for the above usecase.

[sriniprad08]

hi @atcodedog05 ,

Thanks for the reply. The data and the sample workflow created by @DawnDuong  is already there.

[atcodedog05]

Hi @sriniprad08 

You can use transpose tool and summarize to create a dynamic formula.

Workflow:

Hope this helps : )

[sriniprad08]

Hi @atcodedog05 ,

Thank you so much for taking time in helping out. Sorry i didnt understand the workflow or may be i was not clear in my requests.

Can you please explain me in detail about the workflow you added ? Is the below Current formula and the Modified one are the simliar?

Current formula 

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]<10 and [Base_month_plus_2]<10 and [Base_month_plus_3]<10 and [Base_month_plus_4]<10 and [Base_month_plus_5]<10 then "RECOVERING AND WITHIN TOLERANCE"
else "INCREASING UPPER LEVELS OF TOLERANCE"
endif

Modified one.

if [CountNull_Value]>0
then "There are not enough data"
elseif [Min_Change]>0
then "RECOVERING AND WITHIN TOLERANCE"
else "INCREASING UPPER LEVELS OF TOLERANCE"
endif

Thanks,

Sri

[atcodedog05]

Hi @sriniprad08 

Sorry my bad misread it i have made corrections now.

1. Using summarize tool I am getting the max of value.

2. Using summarize tool I am getting the null count too.

Formula

if [CountNull_Value]>0 // If any month columns were null then count would greater than 0
then "There are not enough data"
elseif [Max_Value]<10 // If any month columns were greater than 10 then Max_value would be greater than 10
then "RECOVERING AND WITHIN TOLERANCE"
else "INCREASING UPPER LEVELS OF TOLERANCE"
endif

Hope this helps : )

[sriniprad08]

Hi @atcodedog05 ,

No problem. Thank you so much. One question is this can be dynamic interms of user input. 

For e.g. like below the 5 months are compared suppose user would like to compare last 2 months or 3 months or 4 months etc..

How can we go about it please?

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]<10 and [Base_month_plus_2]<10 and [Base_month_plus_3]<10 and [Base_month_plus_4]<10 and [Base_month_plus_5]<10 then "RECOVERING AND WITHIN TOLERANCE"
else "INCREASING UPPER LEVELS OF TOLERANCE"
endif

[atcodedog05]

Hi @sriniprad08 

You can make changes in the highlighted sample tool. To set it to last 2 or last 3 months.

Hope this helps : )

[sriniprad08]

Hi @atcodedog05 ,

Great. Thank you so much. 🙂 . Will go through it and get back .

Cheers,

Sri