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

Dynamically select and stack the monthly data

Allenli
7 - Meteor

I have a list of monthly data file between December 2019 to May 2020, I want to be able to dynamically select the monthly data and stack the data on the result as it shows below, is it possible to do? I was able to do the first step, but don't know how to dynamically select the months range. Please see attached for example data.

 

Raw Data: Ending balance from the data file

Dec-19Company ACompany BCompany C
Ending Balance51709547862
Jan-20Company ACompany BCompany C
Ending Balance80653479485
Feb-20Company ACompany BCompany C
Ending Balance205076358747
Mar-20Company ACompany BCompany C
Ending Balance805941926106
Apr-20Company ACompany BCompany C
Ending Balance382952161369
May-20Company ACompany BCompany C
Ending Balance219859394807

Step one: Combining and calculating the difference from different monthly files.

Jan-20Company ACompany BCompany C
Beg Balance51709547862
Diff2895-6068-378
End Balance80653479485

Step Two: Dynamically selecting the month on the result. For example, I want to show the data between Dec-19 to Feb-20 or between Jan-20 to Mar-20, how can I do that?

Dec-19Company ACompany BCompany C
Beg Balance96770137615
Diff42042534-6753
End Balance51709547862
Jan-20Company ACompany BCompany C
Beg Balance51709547862
Diff2895-6068-378
End Balance80653479485
Feb-20Company ACompany BCompany C
Beg Balance80653479485
Diff-601541568262
End Balance205076358747

Or

Jan-20Company ACompany BCompany C
Beg Balance51709547862
Diff2895-6068-378
End Balance80653479485
Feb-20Company ACompany BCompany C
Beg Balance80653479485
Diff-601541568262
End Balance205076358747
Mar-20Company ACompany BCompany C
Beg Balance205076358747
Diff6009-3443-2641
End Balance805941926106
2 REPLIES 2
GaneshBo
Alteryx
Alteryx

Hi @Allenli 

 

I hope the attached solution helps. The trick is to transform the data to dynamically manipulate it and do calculations for all the companies. 

 

If you'd like to select the month, I'd recommend using the filtering logic, which would require you to leave an extra date column (as shown below).

GaneshBo_0-1600913261685.png

 

(Note: Since I don't have the data for the beginning balance of December 2019, I left it blank.)

 

Best,

Ganesh

 

 

Allenli
7 - Meteor

Thank you for your reply. I ended up creating a macros to do the calculation and then using the filter to select the date range. Your idea helps a lot on the logic.

Labels