Challenge #221: Avocado Consumption

A solution to last week’s challenge can be found here. Source: www.californiaavocado.com/blog/avocado-fruit-or-vegetable

Love them or hate them, Avocados are a popular food that have been increasing in popularity. Did you know that they are technically a berry?

At any rate, this week's challenge is to find the regions with the largest increase (by percentage) of avocado consumption for [total volume] and each PLU code. PLU codes are the 4 digit codes which classify produce in retail stores. The final output should include both conventionally grown and organic results.

challenge_221_start_file.yxmd

challenge_221_solution.yxmd

Preparation

Data Analysis

Intermediate

Core

Transform

Accepted answers

All comments

johnemery

Nice problem. I don't think my solution is particularly elegant, but it gets the job done, which is the most important part.

Spoiler

View my solution video here:

Spoiler

Challenge 221 Solution jemery.yxmd

Aaron_Harter

Tasty challenge this week:

Spoiler

challenge_221_AB Data_Aaron.yxmd

neilgallen

Not formatted like the solution, but it's there.

Spoiler

challenge_221_NGA.yxmd

Blake

I'd make an avocado joke but it would be pitiful...

Spoiler

Check out my video solution below :

Spoiler

challenge_221_start_file_BH.yxmd

dsmdavid

All those brunches are ballooning the avocado consumption...

Spoiler

david_221.yxmd

Kenda

Although my husband loves them, I am not a fan of avocados!

Spoiler

Perfect use case for @patrick_digan 's Multi-Row Multi-Field tool!

challenge_221_start_file.yxmd

RolandSchubert

Spoiler

Not my favourite berry, but delicious.

challenge_221_solution_rsc.yxmd

ericjed13

I'm so nervous to have this for my first ever Weekly Challenge submission!

Spoiler

challenge_221_solution_ericjed13.yxmd

AngelosPachis

Challenge #221 is done!

Spoiler

challenge_221_solved_AP.yxmd

rmassambane

Good one.

challenge_221.yxmd

RobertW

Didn't use the same method as the others. Also my TV% is off by 100% which seems weird...

Spoiler

challenge_221_start_file.yxmd

patrick_digan

Spoiler

challenge_221_start_file.yxmd

jatterberry

My solution is formatted somewhat differently than the output file provided. I prefer fields in each record to relate to one another, so I left it in the format: region, type, name, value

Spoiler

challenge_221_start_file.yxmd

estherb47

@ericjed13 , welcome to the fun!!!

Cheers,

Esther

jatterberry

Great work in keeping your solution organized and labeled, especially for your first submission! if I had one recommendation going forward, I would say to think of where you could put your union tool to reduce the amount of other tools you need on your canvas (perhaps immediately after your two filters.

Way to go!

cgoodman

Spoiler

Here's my solution. I seem to have solved it a bit differently to others by going down the route of using the multi-row formula and using the groupings to save doing the formula 4 times over.

Challenge 221.PNG

challenge_221_solution.yxmd

TonyA

Here's my solution. I added a few dynamic aspects to the workflow but didn't go too far overboard with it.

Spoiler

challenge_221_TonyA.yxmd

Fun challenge.

Spoiler

There must be a way to use the Arrange tool to configure the top 8 records; Instead of having to do 2 cross-tabs and joining them...

I never use it...

challenge_221_mj_solution.yxmd

ccadwell

It's my first submission too. Sure am glad there is more than one correct solution.

Spoiler

SPOILER

Weekly Challenge #221.JPG

challenge_221_start_file.yxmd

estherb47

Yummy challenge!!

Spoiler

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.

challenge_221_EHB_completed.yxmd

bsanders2009

Just like my first car--it's ugly but it works!

Spoiler

challenge_221_finished.yxmd

ggruccio

I probably made it more difficult than it needed to be! But got it to tie out exactly.

Spoiler

challenge_221_gg_finish.yxmd

pasccout

Here is my solution

challenge_221_my_solution.yxmd

AryCardoso

challenge_221_ary.yxmd

chinta

Spoiler

Solution to the challenge:

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.
Final workflow

challenge_221_end_file.yxmd

Tberney

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

Spoiler

Regions required that I turn the sampling function into a macro and feed in the region count. The transpose tool turns the field names into data so calculating the values for additional SKUs was straightforward, and using the dynamic rename tool I can drive the names. The Transformations strip the % signs so you have to put them back in at the end. All this pushes the totals to the end (because 'T' sorts after the 4-digit SKUs) and i did not bother to figure out how to push them back to the front.

challenge_221_solution_TMB.yxzp

DaisukeTsuchiya

Here is my solution.

Spoiler

challenge_221_start_file_DT.yxmd

alexandramannerings

Spoiler

When life gives you avocados, make guacamole.

challenge_221_aokm.yxmd

My solution:

Spoiler

challenge_221_EW.yxmd

LHolmes

Great problem to stretch the core muscles and play with some transformation tools.

Spoiler

Wasn't particularly detailed in my solution documentation this week but essentially filtered our everything but the beginning and end time periods, pivoted the data, calculated growth, sorted, selected the top for each type/PLU, and the latter part was formatting data to the final (took liberty with names).

challenge_221_solution_lmh.yxmd

T_Willins

Thanks for the Weekly Challenge!

Spoiler

challenge_221_solution.yxmd

Mengdee

great challenge.. can't wait to see some other solutions.. i get the feeling i really butchered mine. Couldn't figure out a nice succinct way to rank each PLU

challenge_221_solution_ml.yxmd

raychase

Challenge #221 is done!

Spoiler

challenge_221_completed_file.yxmd