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.

Common Use Cases

Accepted answers

ImadZidan

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.

example.yxmd

AngelosPachis

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

All comments

ImadZidan

Hello @SH_94 ,

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

Let me know if you need further help.

Brackets.yxmd

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

example.yxmd

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?

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15881

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703