Menu Item Order Rate

cmarutzky
cmarutzky Member
in Charting

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).

Best Answer

  • cmarutzky
    cmarutzky Member
    Answer ✓

    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)

Answers

  • Chips
    Chips Domo Employee

    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?

     

    cg

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • cmarutzky
    cmarutzky Member

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

  • ST_Superman
    ST_Superman Domo Employee

    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`)

    1.png

     

     

     

  • Chips
    Chips Domo Employee

    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.

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • cmarutzky
    cmarutzky Member
    Answer ✓

    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)

  • Chips
    Chips Domo Employee

    #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)

     

    Domo Consultant

    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.

Categories