Need help with a metric count

Elastic Stack Kibana
1

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 :slight_smile:

Thanks in advance

2

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
Screenshot 2019-09-18 at 12.01.39.png686×2030 78 KB

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

3

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

Capture
Capture.PNG335×758 21.9 KB

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.

4

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

5

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:

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:

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

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.

7

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?

8 
 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
}
9

Ah, I see - in this case you have to use a nested object 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.