Aggregating an AVG share of check metric

[samkim87]

Hello,

I am working with Restaurant Point-of-sale data and am struggling to find the most effective way to handle my situation entirely in Alteryx.  A check is made up of many items, I'm trying to determine what portion of the check is made up by items, and then aggregate that over time and into different categories of the menu.

My data table is laid out at the item level.  Each item is assigned a menu segment (burgers, sides, salads, etc...).  

I'm most interested in two metrics; Size of Check when that menu segment is ordered, Share of check that the menu segment made up.  I have other dimensions involved in my dataset that are making the aggregation troublesome for me.  The first is DayPart (when the item was ordered), a particular check can span multiple dayparts.  The second is Ordermode (dine in or take out), a particular check can span multiple order modes (say you have dinner in the restaurant then you take some nachos home with you to feed your dog because you're a bad dog owner).

Month is also a dimension in my table, so seeing this over time is also important.

My queries are consistently inflating Size of Check.  I've attached the workflow that I put together.  My desired end product is a tableau extract that looks like the below image.  This would allow me to calculate AVG Check by dividing CheckSize by # of checks, and then I can handle the rest of the calc's in Tableau.

Any help is appreciated

[JessicaS]

Hello @samkim87

First things first, I want to extend a warm welcome to Alteryx Community! We are glad to have you here and thanks for making your first post with us.

It would be helpful if you could attach another file so that we can take a look at some of your data.  Since your workflow relies on dabase connections we can't get a look at how your data is oriented.

You can do this by adding an output tool right after your data stream out but before your summarize (see below) and uploading that file here.

If your data is private and you would rather not share with the community you could create a 'dummy' file for us to take a look at that mimics the layout of your real data.

[samkim87]

Hi Jessica,

Attached is the output as requested. 

Please let me know if i can provide additional info that would be helpful.

Thanks,

Greg

[JessicaS]

@samkim87,

Could you also provide a sample of OrderModeReplace.xlsx?

Thanks!

[samkim87]

Of course, here it is.

Thanks,

Greg

[JessicaS · Accepted solution]

Hello @samkim87,

I set up your data with the inputs you sent me and looked at your two main questions:  Size of Check when that menu segment is ordered, Share of check that the menu segment made up.

These can be answered by using the summarize tool as I did in the attachment.  I grouped by daypart and segment then calculated the average and median check size along with the average share of total.

Looks like you have the roll up correct to get it into your format in tableau.  If you wanted to roll it up further in alteryx you could remove some of the group by's in your summarize tool.  For example, you could group by just daypart and order mode.

We have some great time series tools available if your interest is to forecast these segments into the future.  

[samkim87]

Thank you Jessica, 

I'll check out your workflow and will follow up if necessary.  I appreciate it.

Greg