# Need help with a metric count

**URL:** <https://discuss.elastic.co/t/need-help-with-a-metric-count/199961>\
**Category:** Kibana\
**Created:** [September 18, 2019, 9:23am UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961 "2019-09-18T09:23:52Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![richard\_hobbs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_hobbs/32/51759_2.png) [@richard\_hobbs](https://discuss.elastic.co/u/richard_hobbs)\
**Post date:** [September 18, 2019, 9:23am UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/1 "2019-09-18T09:23:52Z")

</div>

Hi all, i am new here and i am learning the programming but i am very new so please bear with me and i do hope some kind soul can help me with my troubles.  
I am looking for a metric widget to show a number of products in my data that need building.  
What i am looking for is to grab the SKU of the product + the qty ordered and show the total sum/value for example we could have 2 orders for the same SKU but one order can have 1x SKU and the other order could have 2x SKU so total orders are 2 but i need it to show the 3 as it needs to take the qty ordered into consideration, which gives me the amount of orders but not the total qty over those orders:-

Currentlly the query is as follows:

SELECT count(\*) as Total from orders where  
OrderStatus IN ( 'PendingWaitingAllStock','PendingWaitingPartStock') and  
OrderLines.Product.SKU IN ('4151596')

I need to get the column: "OrderLines.QuantityOrdered" into the metric as that can be more than 1 as explained above.  
So i need to take "OrderLines.Product.SKU" and times/multiply "OrderLines.QuantityOrdered" to give me the total count.

I hope i have explained myself enough its hard as im learning and very new to this so hard to ask the question in tech terms 🙂

Thanks in advance

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [September 18, 2019, 10:03am UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/2 "2019-09-18T10:03:51Z")

</div>

Hi, @richard_hobbs,

it sounds like you need the total sum of `OrderLines.QuantityOrdered` split by `OrderLines.Product.SKU`. You can do that with the metric or data table visualization (go to Visualize and create a new visualization from there).

This is how your config should look like:

 ![39](https://us1.discourse-cdn.com/elastic/original/3X/d/9/d9211b42ecd7953b2522845502232f2b8d163f38.png)

To do a terms aggregation you need to use a keyword-indexed version of your field. It depends on your mapping whether it exists, if you are using the default mapping it should be called like your field with a `.keyword` suffix

---

<div class="post-metadata">

**Author:** ![richard\_hobbs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_hobbs/32/51759_2.png) [@richard\_hobbs](https://discuss.elastic.co/u/richard_hobbs)\
**Post date:** [September 18, 2019, 10:10am UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/3 "2019-09-18T10:10:20Z")

</div>

Hi,  
thank you for your help, this looks promising however when i create a metric i dont get half of those options.

 ![Capture](https://us1.discourse-cdn.com/elastic/original/3X/0/e/0e8de3b04abacd151c3bf8be137bff3a94ac0f19.png)

Maybe my kibana is not updated? its on 7.2.0 i believe 7.3 is the latest might have something to do with it?

Thank you for your help so far i really appreciate it.

---

<div class="post-metadata">

**Author:** ![richard\_hobbs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_hobbs/32/51759_2.png) [@richard\_hobbs](https://discuss.elastic.co/u/richard_hobbs)\
**Post date:** [September 18, 2019, 10:45am UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/4 "2019-09-18T10:45:38Z")

</div>

Ah ignore that sorry i re read and its done in Visualize is there anyway of getting that then into canvas?

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [September 18, 2019, 2:01pm UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/5 "2019-09-18T14:01:25Z")

</div>

Ah sorry, missed that. I think currently you can't display multiple values in a single metric in cabvas, you would have to use the data table for that.

For a data table, this query should do the trick:

```auto
SELECT OrderLines.Product.SKU as SKU, sum(OrderLines.QuantityOrdered) as Total from orders where
OrderStatus IN ( 'PendingWaitingAllStock','PendingWaitingPartStock') GROUP BY OrderLines.Product.SKU

```

If you want to use the metric visualization, then you have to create separate elements for each SKU with such queries:

```auto
SELECT sum(OrderLines.QuantityOrdered) as Total from orders where
OrderStatus IN ( 'PendingWaitingAllStock','PendingWaitingPartStock') and
OrderLines.Product.SKU IN ('4151596')

```

---

<div class="post-metadata">

**Author:** ![richard\_hobbs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_hobbs/32/51759_2.png) [@richard\_hobbs](https://discuss.elastic.co/u/richard_hobbs)\
**Post date:** [September 18, 2019, 3:15pm UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/6 "2019-09-18T15:15:53Z")

</div>

Thank you very much sorry i was not clear enough in my OP i am looking for a total of 1 specific SKU so using the metric one should work just testing it out.  
Thank you fro your help on this i appreciate it.

---

<div class="post-metadata">

**Author:** ![richard\_hobbs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_hobbs/32/51759_2.png) [@richard\_hobbs](https://discuss.elastic.co/u/richard_hobbs)\
**Post date:** [September 18, 2019, 3:50pm UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/7 "2019-09-18T15:50:52Z")

</div>

It almost worked haha if there is an order with 3 other products on that one order its taking all the OrderLines.QuantitityOredered and adding them all up so for example instead of showing 5 its showing 20 as there are 4 lines in total all have 5 qty. even though the code you put has a specific SKU its seems to be ignoring that?

---

<div class="post-metadata">

**Author:** ![richard\_hobbs](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/richard_hobbs/32/51759_2.png) [@richard\_hobbs](https://discuss.elastic.co/u/richard_hobbs)\
**Post date:** [September 18, 2019, 3:52pm UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/8 "2019-09-18T15:52:07Z")

</div>

```
 OrderLines {
  "Product": {
    "ProductID": 4151122,
    "SKU": "KIT15-34"
  },
  "OrderedProductSKU": "KIT15-34",
  "OrderedProductName": "Kit 15 Brass | Gold | 38mm",
  "QuantityOrdered": 5,
  "QuantitySent": 5,
  "QuantityReturned": 0,
  "LineStatus": "Allocated",
  "SingleItemPrice": 12.49,
  "TotalItemPriceExVat": 62.45,
  "TotalItemPriceIncVat": 74.94,
  "VatTotal": 12.49,
  "RegularPrice": 12.4917,
  "TaxRate": 20,
  "Discount": 0,
  "IsComponent": false,
  "IsKit": true
},
{
  "Product": {
    "ProductID": 2049356,
    "SKU": "PLU92474"
  },
  "OrderedProductSKU": "PLU92474",
  "OrderedProductName": "Gold 3 Core With In Line Switch & Plug 2.5m Long PLU92474",
  "QuantityOrdered": 5,
  "QuantitySent": 5,
  "QuantityReturned": 0,
  "LineStatus": "Allocated",
  "SingleItemPrice": 0,
  "TotalItemPriceExVat": 0,
  "TotalItemPriceIncVat": 0,
  "VatTotal": 0,
  "RegularPrice": 0,
  "TaxRate": 20,
  "Discount": 0,
  "IsComponent": true,
  "IsKit": false
},
{
  "Product": {
    "ProductID": 2069859,
    "SKU": "PLU33458"
  },
  "OrderedProductSKU": "PLU33458",
  "OrderedProductName": "38mm Brass Spacer Male & Female 10mm Threads [SU1990 PLU33458]",
  "QuantityOrdered": 5,
  "QuantitySent": 5,
  "QuantityReturned": 0,
  "LineStatus": "Allocated",
  "SingleItemPrice": 0,
  "TotalItemPriceExVat": 0,
  "TotalItemPriceIncVat": 0,
  "VatTotal": 0,
  "RegularPrice": 0,
  "TaxRate": 20,
  "Discount": 0,
  "IsComponent": true,
  "IsKit": false
},
{
  "Product": {
    "ProductID": 4151603,
    "SKU": "4151603"
  },
  "OrderedProductSKU": "4151603",
  "OrderedProductName": "Kit 15 Brass STOCK KIT\t",
  "QuantityOrdered": 5,
  "QuantitySent": 0,
  "QuantityReturned": 0,
  "LineStatus": "OutOfStock",
  "SingleItemPrice": 0,
  "TotalItemPriceExVat": 0,
  "TotalItemPriceIncVat": 0,
  "VatTotal": 0,
  "RegularPrice": 0,
  "TaxRate": 20,
  "Discount": 0,
  "IsComponent": true,
  "IsKit": false
}
```

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [September 19, 2019, 6:49am UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/9 "2019-09-19T06:49:47Z")

</div>

Ah, I see - in this case you have to use a [nested object](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html) data type. Change the mapping of your index as described in the linked document, then it should select only the order lines you are looking for.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [October 17, 2019, 6:49am UTC](https://discuss.elastic.co/t/need-help-with-a-metric-count/199961/10 "2019-10-17T06:49:50Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.