Challenge #419: Cost Analysis of Easter Baskets

[AGilbert]

UPDATE:

I've created a Topic in the Designer Desktop here with a simple example. Please divert replies to that location. 

Original:

The initial merge of tables is fairly standard, but the second half of the workflow supports dynamic calculation/reporting. My goal was to handle future data and reports for additional years. If you have feedback, I'm all ears (actually eyes).

The goal has been to keep the reporting metrics but advanced by one when a new year is added to the data. And to avoid manual renames and select/deselect of fields. 

Question: I haven't yet figured out how to dynamically update the fields for the first check. Ideally, I'd want to drop "2021 to 2022" in addition to appending "2023 to 2024" when adding new data to the input. For reference, I've added a "*_test.yxmd" file where 2024 data is included.

Instead of an upstream sort/sample I've tried to implement the following two expressions in dynamic select/replace tools but am in new territory. Any ideas? 

1. contains([_CurrentField_], "most recent year')) 

2. [FieldNumber] IN range(n, n-1) where n is the count of fields (this is to dynamically get the last two columns) 

Thanks in advance! That was a fun one. 

-AG

- initial join

Spoiler

-dynamic workflow

Spoiler

[jeprime]

I also had to adjust my workflow because of this. The rounding must wait until the reporting happens.

[Macostonis]

Got it!

[Powerhouse_21]

Spoiler

 

[Watermark]

AG - Saw your post and decided to turn around and tweak my reply to make it 'dynamic solution' which can be used as is going forward. 

This changes dynamically changes the most recent to 'current' and current -1 to 'prior'  current -2 for 'previous prior' (I know, original) , then does the calcs off the current & prior for 'next'. 

Can add additional tweaks, but this does effectively accomplishes the goal.  One of the drawbacks is losing the actual year(s). I did have a version where I left 2021 alone, but that would require an intervention for what I now label as 'previous prior'.   

See attached. 

Spoiler

 

 

[Qiu]

@AYXAcademy 
I almost forgot I have submitted this one since it is 1.5 years ago.
Glad to see it is now published. 😁

[gawa]

You are a frequent contributor, @Qiu !

Spoiler

Could not match exactly for task 2.

[Watermark]

Taking this one step further for AG, 

I added in a couple more tools simply to capture the naming convention of having the years included for the header names.  See added yellow tools. 

Spoiler

[julian_garcia_quevedo]

My solution here

[DaisukeTsuchiya]

Spoiler