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

Pivot (Ageing bracket) and Percentage Calculation

SH_94
11 - Bolide

Hi Community,

 

I would like to build the workflow with the following condition and below is the screenshot of the data needed.

1. Pivot the total amount per invoice to get full amount for each invoice

2. Based on the full amount and number of days ( Final Date - Initial Date) obtained, pivot it to become the table as shown below.

3. Based on the total amount obtained , calculate the percentage for each invoice.

 

May i know how you guys will build the workflow to ensure all the data are captured. Appreciate if you can the working of alteryx for this.

 

Jacob_94_0-1614975362340.png

 

 

5 REPLIES 5
clmc9601
13 - Pulsar
13 - Pulsar

Hi @SH_94,

 

Does this workflow produce the output you'd like?

 

Screen Shot 2021-03-05 at 9.50.17 PM.png 

If your brackets and invoice data will be coming from the same file, you can separate them based on record id with select records or with a filter tool. I hope this helps!

SH_94
11 - Bolide

Hi @ clmc9601,

 

Thank you for your time.

 

As i quite new to the Alteryx , some of the functions i not sure where to click it and how to use it.

 

Could you briefly explain  how to use these tools and where can i find it for the items that circled in black 1 ,2 and 3 screenshot below?

 

Jacob_94_0-1615006911558.png

 

clmc9601
13 - Pulsar
13 - Pulsar

Hi @SH_94,

 

Yes, these tools are a little bit complicated! The gray ones come from the developer tab. Alteryx has some great articles and resources available, which I have linked here.

 

Black circle 1. Dynamic replace

Here is the tool mastery article

Effectively, this tool is evaluating the boolean expression (the second dropdown) to see whether the days for each invoice fall within which bracket. Based on the results, it does a certain action (the third dropdown) to a specific field in the data (first dropdown).

 

2. Append fields, multi-field formula

Append fields here effectively joins the ageing bracket columns to the data. In normal use cases, append fields multiplies the two datasets together. Here is an article about it.

Multi-field formula is just a formula tool that can apply to multiple fields at once. In this case, I applied a formula to populate the new amounts if the bracket was the same.

 

3. Dynamic rename - renames the fields based on a pattern. Since crosstab removes all special characters from a field name, I use dynamic rename to grab the original titles for the brackets. The second dynamic rename adds the word "amount" to each of the field names. Here is an article.

 

I hope this helps!

SH_94
11 - Bolide

Hi @ clmc9601,

 

Thanks a lot for the great explanation.

 

I will spend some time to digest it and understand how to use the tool effectively. 

 

That's really helpful. And will get back to you if i have any questions after reading through the article and navigate around the tools.

clmc9601
13 - Pulsar
13 - Pulsar

Sounds good. I'm happy to try and answer any specific questions or if you can't find the answers in the existing resources in the community.

 

If this workflow ends up working for your use case, I would greatly appreciate if you marked it as a solution! This also benefits future community members with a similar question. Thanks!

Labels