Calculating previous month based on current month selection

[PN2527]

I have built the attached workflow. Now, the requirement is that I need to convert it into an analytic app and allow the user to select any month of any year. Based on the selected month, the previous month of that year should get auto-calculated. The rest of the calculations shall remain the same.

[BrandonB]

The trick to dynamic calculations is to avoid hard coded formulas that reference columns that have their names change. The way to address this in Alteryx is to use multi-row formulas and in your case, also a dynamic filter that brings in the date selected through the interface tool as well as the previous month. Screenshot is below and the analytic app example has been attached. Please let me know if you have any questions!

 

[BrandonB · Accepted solution]

I made one quick tweak to it just in case you have data that spans over multiple years. Updated workflow is attached:

[deviseetharaman]

The attached workflow is not working. The filter is broken.

[deviseetharaman]

Please check if this workflow works for you. Not as elegant as the one marked as Solution. This still involves hardcoded formula.

[BrandonB]

@deviseetharaman the filter isn't broken, I just didn't have an output specified to be rendered because I wanted them to have the flexibility to add whatever output they wanted at the end. Also, the sample data provided only has February and March data in it, so it won't create an output unless you pick a date in March so that it has a month to look back at.

Screenshot of render addition is below and workflow is attached. 

 

[deviseetharaman]

@BrandonB, what i meant by filter broken is due to the year calculation. It will go for a toss if we select Jan as my desired date. To check the logic, please change the period of Ist row as Dec 2019 and 2nd row as Jan 2020. 

Thank you for showing me the right way to solve the issue. I am not so good with Mutli row and Multi field tools 😞 

[BrandonB]

Ah, good catch! I need to modify the selection of the current and previous month slightly to account for situations where one month is in a year and the previous month is in the prior year.

[PN2527]

Hi Both,

Thanks for looking into this.

I have modified the workflow in order to accommodate the following changes:

- Rolled the data to end of the month instead of first of the month

- Did union with another file to get an account and amt details

- Added totals macro add the end (Though not sure if this macro will run fine on the server.)

- Added data for march and april months

There is another requirement where we need to add the following two more columns:

1. IN1 - Formula: IF([FEB_2020_Calc]=0,[Change in Amt],ROUND([Change in Vol]*[FEB_2020_Calc],0))

2. IN2 - Formula: [Change in Amt] - [IN1]

The revised analytic app is attached. Can you please help with the above requirements as well?

Thanks!