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.

10

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