Menu Item Order Rate

We are attempting to design a card that displays the order rate (Qty Sold/100 Guests) for different categories of menu item. My issue is that the traffic measure is duplicated for each item sold so the calculation works at the detail level but not always at the summary level (Category totals). We tried a sum(distinct) function but sometimes the traffic measure across dates is the same so this fails.

And here is a card I built to illustrate the issue:

https://pappas.domo.com/page/287596754/kpis/details/962548900

You can see that over the two days, traffic is the same so the actual Qty/HC should be 12.78 (79/618*100).

Find more posts tagged with

Accepted answers

cmarutzky

I will need to test this but it also gave me a another idea to test. If I create a key on the lowest level detail (date_revenuecenter_daypart) then I should be able to sum the traffic column and then divide by count(distinct key)

All comments

Chips

Good morning @cmarutzky, can you share a screenshot or upload an example of the dataset?

And how are you wanting to use the aggregations? Summary number? Data label?

cmarutzky

Not sure of the best way to show the data. This is a screen shot of the example card with the columns.

Capture.PNG

ST_Superman

I'm not sure if it will work for your larger data set, but I was able to use this formula to get to your desired results with your sample data:

(sum(`Qty`) / sum(`Traffic (Sum)`)) * COUNT(DISTINCT `Date`)

Chips

If what @ST_-Superman-_ posted doesn't work, then I am happy to dig a little deeper.

And sorry @cmarutzky for the delay in response, it looks like my last correspondence didn't post to the thread. But no excuses!

Which item is our category on this dataset? And what level of date granularity is ideal?

That is to say, as currently displayed in your png, we might be able to use some beastmodes to aggregate the data depending on which fields your users need to see.

This would probably be easier if I could access your instance, unfortunately I can't. So if you could just build a quick table of how you want the ideal card to look I can take a stab at some beastmode aggregations.

cmarutzky

Chips

#Math

Love it! Let us know how it goes

@cmarutzky wrote:
I will need to test this but it also gave me a another idea to test. If I create a key on the lowest level detail (date_revenuecenter_daypart) then I should be able to sum the traffic column and then divide by count(distinct key)