We're actively looking for ideas on how to improve Weekly Challenges and would love to hear what you think!Submit Feedback
Used many more grouping fields in the Multi-row formula tool than I had done before, and eliminated the need for several other tools in doing so (extra sorts, crosstabbing, formula tool). Not entirely dynamic because I had to move the fields around with a select at the end to match output.
This could have been straightforward had there been no constraint on the output model. Still I was able to modify, but wish there's isn't any model constraint.
So, here's what I did,
1. Calculated the min and max periods (Year + Month).
2. Selected min and max periods only.
3. Using Crosstab, I was able to break the model by PLU codes.
4. Calculated max %consumption.
5. Merged each crosstab outputs using Join Multiple tool.
I don't feel this workflow is efficient, but nevertheless it works.
The real challenge here seems to be in making the WF dynamic - meaning to be able to adapt to changes in the number of regions, types, or SKUs. As it happens, types are handled naturally by the approach I took. See inside for how I approached the others