Multiple Ageing Bracket and Percentage

[SH_94]

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 .

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

And the result will be as follow:

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.

[AngelosPachis · Accepted solution]

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.

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

Cheers,

Angelos

[SH_94]

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.

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]

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]

@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]

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

Many thanks again for the help.

[AngelosPachis]

@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]

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?

[AngelosPachis]

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.

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]

@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?