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

Multiple Ageing Bracket and Percentage

SH_94
11 - Bolide

Hi Community,

 

I have the data which consist of multiple ageing bracket criteria and percentage for each item as per screenshot below. Each group of data and type consist of different ageing bracket and percentage .

Jcsh_66_0-1618038233243.png

 

I would like to use the above criteria to calculate ageing months and discount for the following data as per screenshot below.

Jcsh_66_1-1618038466029.png

 

 

And the result will be as follow:

Jcsh_66_2-1618038508308.png

 

May i know if there is anyway to build the workflow more dynamically based on the criteria - Data A?  I had attached the desired result in the excel file below.

12 REPLIES 12
AngelosPachis
16 - Nebula

Hi @SH_94 ,

 

At which stage are you at in terms of building the workflow? Is there any particular point that you got stuck?

 

I have created a workflow that answers your question, but the purpose of the community is to help you in points you get stuck, not just do things for you although that's very nice and convenient.

 

I would suggest you try building this yourself by looking at the tools used and if you get stuck then use the solution. In this way, you will better understand the logic behind it and what the tools do or why those tools have been chosen.

 

 

AngelosPachis_0-1618045718319.png

 

Let me know if you have any questions on the workflow.

 

Cheers,

 

Angelos

SH_94
11 - Bolide

Hi @AngelosPachis ,

 

Thank you for the prompt response.

 

I have build the workflow as per screenshot below but i feel like it is time consuming for me to split one by one as per screenshot below. My original data have around 15 criteria which i think it is not so dynamic if i build in this way. Hence, i would like to ask for assistance if there is more fastest or dynamic way to build the workflow so that if my original keep increasing the criteria , i would not need to build the workflow so long and rigid.

Jcsh_66_0-1618047165839.png

 

Currently i still digesting on how you build the workflow and let you know if i encounter anything.

 

Thanks again for the sharing of the workflow. Appreciate it a lot.

 

SH_94
11 - Bolide

Hi @AngelosPachis ,

 

May i know how u able to convert the data from excel into the text input ? Is it we need to do it manually or we can do it fastest way?

 

 

Thank you

AngelosPachis
16 - Nebula

@SH_94 Copy a range of cells from excel and pasting into Alteryx will automatically create a Text Input tool with the selected range of cells.

 

I did it because when all 3 tables (Data 1/2 and Output) are in the same excel sheet, if you bring the data in Alteryx you will get quite a messy table with nulls all over the place. It's easier to convert them to a text input then spending time cleaning the table and removing the nulls

SH_94
11 - Bolide

Hi @AngelosPachis ,

 

Thank you for the prompt response.

 

I am sorry that i didn't explain clearly in the initial post on what i try to achieve in the workflow.Basically, i would like to build the workflow which can present in the format below (example). I know how to build the workflow if just calculate the ageing bracket for days or months. However, i didn't know how to build the workflow if there are two condition in this case : one is Group and another one is type.  

 

May i know is it possible if we build the workflow as per format below (with consideration of type and group ).

Jcsh_66_0-1618048616677.png

 

Many thanks again for the help.

 

AngelosPachis
16 - Nebula

@SH_94  I don't thing that the Type or Group fields will influence the creation of the Ageing brackets, as the expression you have attached only references a single column, which in your example is days but in our example is months). 

 

That means that if you apply the same expression in a  formula tool and reference the [Months] column, that should still convert your ageing brackets in the format that you wish

 

<lower boundary-upper boundary>

SH_94
11 - Bolide

Hi @AngelosPachis ,

 

Kindly refer to the attached workflow that i have built. 

 

May i know how should i fix the error for the Malformed if statement in this case?

Jcsh_66_0-1618051434509.png

Jcsh_66_1-1618051457058.png

 

 

 

AngelosPachis
16 - Nebula

Hi @SH_94 ,

 

I have made notes on what was wrong in your IF statement, but I'm sure hardcoding everything into a single big IF statement is not the best solution as if something changes in the future, that IF statement is rather static and rigid.

 

I have also created the statements for when the Group is equal to 2 and Type=D or Group= 3 and Type=E, so you can go on and change the values there.

 

 

Untitled.png

 

A better alternative would be to use a look up table for each group and type, where you define the different age brackets by defining the maximum month for each bracket, or something similar to what I did in the workflow I sent over in my first example, where I used the generate rows tool.

 

Hope that helps,

 

Angelos

 

 

AngelosPachis
16 - Nebula

@SH_94 May I ask what's the reason you want to create that big If statement?

 

I thought you had the different ageing brackets from the Data A input? Why do you have to hardcode it in a formula tool?

Labels