Want to get involved? We're always looking for ideas and content for Weekly Challenges.
SUBMIT YOUR IDEA1. Select required columns TicketID, Date, Desc from Point of Sale data set.
2. Filter out data by Date criteria (date since June 2013).
3. Join with Lookup table by Desc to get Type (category of food).
4. Go through each record to flag is_pizza_or_burger, is_side, is_drink with value 1 and 0.
5. Aggregate data to get max value of is_pizza_or_burger, is_side, is_drink for each TicketID group (Potential Meal Deal).
6. Go through each aggregated record to falg is_meal_deal with value 1 when each is_xxx flags is 1, otherwise 0.
7. Aggregate data set from setp 6 by TicketID to get total "Potential Meal Deal" count and by is_meal_deal to get total is_meal_deal count .
8. Calculate and show results:
"Potential Meal Deal" - total TicketID count from step 7.
"Total" - total is_meal_deal count from step 7.
"%" - [Total]/[Potential Meal Deal]
Posting as part of Onboarding training.
MySolution 008
Instead of having a separate lookup table, I filtered for the relevant categories and then created a filter for the 3 relevant scenarios.
Separate from the solution, also ended up appending the total unique ticket IDs from Jul 2013 onwards in order to calculate the percentage.