Customer Ageing date and age Grouping in Alteryx

[SH_94]

Hi all,

I face the roadblock in building the workflow as per the screenshot below:

Firstly,  i would like to calculate the ageing days (between initial and final date).

Once i have the ageing days, i would need to present it in each ageing bracket as shown in the screenshot below.

May i know how can i build the workflow on this part and appreciate if you can guide me on this.

[ImadZidan]

Hello @SH_94 ,

This will give you a kickstart and you can build on it.

Let me know if you need further help.

[AngelosPachis]

Hi @SH_94 ,

You can estimate the difference in days via a formula tool, using a function called DatetimeDiff. Then depending on that value, you can create your column headers if that day is less than 30, 60 or greater than 60 and finally with a cross-tab and join tools you can bring them in the desired format

Hope that helps,

Angelos

[SH_94]

Hi @AngelosPachis

Could you explain how to use the function of the cross tab tool?

[SH_94]

Hi @AngelosPachis

Would like to ask how we convert the null to zero value or do we need to change it?

Will it impact anything on the next workflow if we didn't change to zero?

[ImadZidan · Accepted solution]

Hello @AngelosPachis ,

I have  updated your workflow if you don't mind. @SH_94  is asking for extra help. His tagging to names does not see to be working and in case he is in a rush for an answer.

Please feel free to take it from here and validate what I have updated as it is your workflow after all.

[AngelosPachis · Accepted solution]

That's absolutely fine @ImadZidan , thanks for checking.

@SH_94 The cross-tab will allow you to move your data from a vertical column to a horizontal axis.

At the input anchor of the Cross tab tool, you can see that each amount has a header assigned to it.

We wish to create new columns based on the field [Header], so those will become our column headers after the cross-tab. Then we want to populate the values underneath those 3 columns (<0-30>,<31-60>,<60>) with whatever is contained originally in the [Amount] column. This is how to configure the tool bottom-up (you also have to select an aggregation method for the tools to work)

Finally, we have to go up and group on all values that we don't want to lose in the cross-tab; that is the fields for [Customer] and [Invoice] which we will use for the join further down the line.

Hope that helps in a way, I trust that @ImadZidan has answered the "How to replace the nulls with 0s" question. If that influences the workflow you will build further downstream depends on the workflow you want to build. It may influence it or it may not.

Regards,

Angelos

[AngelosPachis]

Hi @SH_94 ,

Has this worked for you? Is there anything else we can do to help on the topic?

Cheers,

Angelos

[SH_94]

Hi @ AngelosPachis,

May i know how can i amend if i got two figures as shown in the screenshot below?

Previously i choose amount (column G) for the value wanted but now i want to choose Amount 2 ( column F) . But the data keep reading the result from column G and would like to know how I can amend it ?

Is it correct that i amend it from the second screenshot picture below?From the screenshot below, i want the value of the " BaseCurEquiv" but the alteryx keep reading the " RemAmount". May i know how should i change it?

[SH_94]

Hi @ ImadZidan,

Would like to ask how should i build the workflow if i want the percentage calculation as shown in the diagram below.

For instance, if the amount sitting under <31-60 > bracket ,i would need to multiply the amount with 50% and the amount calculated will be shown in the result K. I have also attached the excel working for your reference