get number of orders per store per week in buckets

Stephen_Theunis · 04-23-2020 06:53 AM

Hello,

I want to get the number of orders per store per week in buckets, using dimensions: week, store_id and measure: orders.

Currently, 1 row shows the week, the store_id and the number of orders for that week and store. Now I want to count how many stores have less than 50 orders per week, how many have between 50-100, and how many have more than 100 orders per week.

For instance, week 1 had 32% of stores with less than 50 orders per week, 40% between 50-100, and 28% > 100.

This article suggested to build an NDT to convert the measure of orders to a dimension, after which I can create the buckets. However, my original data is on day level, so I already need to build an NDT to get the measure on week level per store, so that would require me to build an NDT based on an NDT. Is there another way of doing this?

Many thanks,

Steve

Cyril_MTL_Analy

Hi @Stephen_Theunis,

Quick question here, why do you need 2 NDTs?

You could just you build a single NDT with the week, store_id and number of orders.
Essentially something like this:

view: store_week_order_facts {
  derived_table: {
explore_source: orders {
  column: store_id {field: store_id}
  column: orders_created_week {field: created_week}
  column: weekly_orders {field: orders.total_orders}
}

Stephen_Theunis

Hi Cyril,

Thanks for your respone. That was my first attempt as well, but the problem is I need to count the number of stores with weekly orders inside a certain bucket.

A bit more in detail:

First I create an NDT to make a dimension out of weekly orders and put them in buckets of weekly orders with certain thresholds. The result will be the number of weekly orders per store per week per bucket.

Next, what I need is the count of stores per week within each of these buckets. This step is the second aggregation (a count) I need to do for which I need to create the second NDT. My question is, is there an other way to get this count, without creating a second NDT.

Many thanks,

Steve

Stephen_Theunis

This article turned out to be a part of the solution. I added in a distinct count for store_id --> pivoted the created tier --> showed row totals --> create table calculation for percentage of total per week.