Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Revenue Recognition Schedule with multiple contract values

SArielle
7 - Meteor

Hi All, 

 

I have a question about how to create an revenue recognition schedule based on multi-year contracts with step up revenues.

I would like to create a workflow that could turn it into a monthly recognition report. I am currently doing this manually and it becomes excruciatingly painful 

 

Input :

SArielle_0-1681191029983.png

 

Output

SArielle_1-1681191071118.png

 

Thanks in advance!!!

2 REPLIES 2
Yoshiro_Fujimori
15 - Aurora
15 - Aurora

Hi @SArielle ,

Here is one way of doing this.

I assume the values in Y1, Y2 columns are always split to 12 months.

It that is not the case, you may want to tweak the formula tools. Good luck!

 

Workflow

Yoshiro_Fujimori_0-1681194770672.png

Output

Yoshiro_Fujimori_1-1681194833755.png

 

PangHC
12 - Quasar

 

here the workflow. 

 

Pang_Hee_Choy_0-1681195131830.png

 

explanation by tool:

1. recordId - avoid duplicate same customer with multiple project

2. formula - calculate # month for row generate

3. row generator - generate all month

4. cross tab - to assign year to correct month (as cant use column by condition)

5. filter - assign to correct year value to month

6. summary tool + join tool - add number of months per year (handle for month less than 12, okay to remove if not relevant)

7. formula - get date for pivot later and divide value by #month (or 12 if all are full year)

8. cross tab - pivot

9. dynamic rename - change the header to nicer format 

 

 

In summary:

generate months and assign right value, then pivot it. 

 

 

Labels
Top Solution Authors