# Kibana Vega : Sum of field of latest unique values
**URL:** https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047
**Category:** Kibana
**Tags:** vega
**Created:** [February 21, 2023, 10:02am UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047 "2023-02-21T10:02:39Z")
**Posts on this page:** 11
**Page:** 1
### Author:  [@ankitdevnalkar](https://discuss.elastic.co/u/ankitdevnalkar)
#### Post date: [February 21, 2023, 10:02am UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/1 "2023-02-21T10:02:39Z")
**Problem statement :** I have written a Kibana Vega script to show sum of latest `balanceusd` field having a unique `orgid`. I am getting balance logs frequently and every latest log has the updated balance. There can be a number of wallets in one organisation(orgid). Currently, it is showing whatever latest amount comes in `balanceusd` field.
**Expected output :** a metric visualization that shows total net-worth of an organization. It means it should sum all the latest `balanceusd` data across different wallets under an `orgid`
Vega script :
```auto
{
$schema: https://vega.github.io/schema/vega/v3.0.json
title:
{
"text": "Networth",
"fontSize": 30,
"dy" : 20
}
data: [
{
name: latestTotalBalance
url: {
%context%: true
%timefield%: @timestamp
index: wallet-*
body: {
aggs: {
label: {
terms: {
field: event.data.orgid
size: 10
order: {_key: "desc"}
}
aggs: {
balance: {
top_hits: {
_source: event.data.balanceusd
size: 10
sort: [
{
@timestamp: {order: "desc"}
}
]
}
}
}
}
}
size: 0
}
}
format: {property: "aggregations.label.buckets"}
transform: [
{
type: aggregate
ops: ["sum"]
fields: ["balance.hits.hits[0]._source.event.data.balanceusd"]
as: ["networth"]
}
]
}
]
marks: [
{
type: text
formatType: number
from: {data: "latestTotalBalance"}
encode: {
update: {
title: "Networth"
text: {signal: "format(datum.networth, ',')"}
align: {value: "center"}
baseline: {value: "middle"}
xc: {signal: "width/2"}
yc: {signal: "height/2"}
fontSize: {signal: "33"}
"fill": {"value": "white"}
"fontWeight":{"value": "bold"}
text: {signal: "format(datum.networth, ',.2f')"}
}
}
}
]
}
```
---
### Author:  [@ankitdevnalkar](https://discuss.elastic.co/u/ankitdevnalkar)
#### Post date: [February 23, 2023, 9:01am UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/2 "2023-02-23T09:01:24Z")
Can someone help me?
---
### Author:  [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)
#### Post date: [February 23, 2023, 9:51am UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/3 "2023-02-23T09:51:49Z")
I suspect the problem is the text color. Can you try to change this last bits (also adding the currency to format):
> [@ankitdevnalkar](#):
>
> ```auto
> "fill": {"value": "black"}
> "fontWeight":{"value": "bold"}
> text: {signal: "format(datum.networth, '$ ,.2f')"}
>
> ```
---
### Author:  [@ankitdevnalkar](https://discuss.elastic.co/u/ankitdevnalkar)
#### Post date: [February 23, 2023, 10:15am UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/4 "2023-02-23T10:15:09Z")
@Marco_Liberati the output value still shows the same as before. it changed the output format a bit though
New output :
---
### Author:  [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)
#### Post date: [February 23, 2023, 1:21pm UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/5 "2023-02-23T13:21:43Z")
ah ok, you're using the dark theme, that is why of the `white` color.
On my light theme I could not see anything. Probably removing the `fill` will make it adapt automatically to the final user's theme.
Beside that, the problem in your case I think it's that in the transform `fields` you're only pointing to the first element of the array, while it should iterate thru all of them. To solve this issue, you need to adapt the `format.property` value to:
```auto
format: {property: "aggregations.label.buckets[0].balance.hits.hits"}
```
Now in the `fields` just specify `_source.event.data.balanceusd` and the sum aggregation should do it.
---
### Author:  [@ankitdevnalkar](https://discuss.elastic.co/u/ankitdevnalkar)
#### Post date: [February 23, 2023, 1:41pm UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/6 "2023-02-23T13:41:21Z")
@Marco_Liberati I tried above changes but still value is same. It is just showing whatever latest value comes in `balanceusd` field. I manually made a sum for all wallets in which are coming under `orgid 519` and the total is `$38223.45`. Ideally this should be the result of this metric visualisation.
---
### Author:  [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)
#### Post date: [February 23, 2023, 1:43pm UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/7 "2023-02-23T13:43:29Z")
If you click on `Inspect > View: Requests => View: Vega debug => Spec` in the resulting `values` field how many entries are present?
---
### Author:  [@ankitdevnalkar](https://discuss.elastic.co/u/ankitdevnalkar)
#### Post date: [February 23, 2023, 1:48pm UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/8 "2023-02-23T13:48:09Z")
Looks like one entry
```auto
{
"$schema": "https://vega.github.io/schema/vega/v3.0.json",
"title": {
"text": "Networth",
"fontSize": 25,
"dy": 20
},
"data": [
{
"name": "latestTotalBalance",
"format": {
"property": "aggregations.label.buckets[0].balance.hits.hits"
},
"transform": [
{
"type": "aggregate",
"ops": [
"sum"
],
"fields": [
"_source.event.data.balanceusd"
],
"as": [
"networth"
]
}
],
"url": {
"index": "wallet-*",
"body": {
"aggs": {
"label": {
"terms": {
"field": "event.data.orgid",
"size": 10,
"order": {
"_key": "desc"
}
},
"aggs": {
"balance": {
"top_hits": {
"_source": "event.data.balanceusd",
"size": 1,
"sort": [
{
"@timestamp": {
"order": "desc"
}
}
]
}
}
}
}
},
"size": 0,
"query": {
"bool": {
"must": [
{
"range": {
"@timestamp": {
"gte": "2023-02-22T13:30:00.000Z",
"lte": "2023-02-23T13:41:56.948Z",
"format": "strict_date_optional_time"
}
}
}
],
"filter": [
{
"bool": {
"should": [
{
"match": {
"event.data.orgid": "519"
}
}
],
"minimum_should_match": 1
}
},
{
"match_phrase": {
"event.category.keyword": "wallet_balance"
}
}
],
"should": [],
"must_not": []
}
}
}
},
"values": {
"took": 902,
"timed_out": false,
"_shards": {
"total": 115,
"successful": 115,
"skipped": 92,
"failed": 0
},
"hits": {
"total": 3320,
"max_score": null,
"hits": []
},
"aggregations": {
"label": {
"doc_count_error_upper_bound": 0,
"sum_other_doc_count": 0,
"buckets": [
{
"key": 519,
"doc_count": 3320,
"balance": {
"hits": {
"total": {
"value": 3320,
"relation": "eq"
},
"max_score": null,
"hits": [
{
"_index": "wallet-519-wallet_balance-2023.02",
"_id": "DT2AfoYB5kMf-AXvyguo",
"_score": null,
"_source": {
"event": {
"data": {
"balanceusd": "92.67420678"
}
}
},
"sort": [
1677159615043
]
}
]
}
}
}
]
}
}
}
}
],
"marks": [
{
"type": "text",
"formatType": "number",
"from": {
"data": "latestTotalBalance"
},
"encode": {
"update": {
"title": "Networth",
"text": {
"signal": "format(datum.networth, '$,.2f')"
},
"align": {
"value": "center"
},
"baseline": {
"value": "middle"
},
"xc": {
"signal": "width/2"
},
"yc": {
"signal": "height/2"
},
"fontSize": {
"signal": "33"
},
"fill": {
"value": "white"
},
"fontWeight": {
"value": "bold"
}
}
}
}
],
"config": {
"range": {
"category": {
"scheme": "elastic"
}
},
"arc": {
"fill": "#54B399"
},
"area": {
"fill": "#54B399"
},
"line": {
"stroke": "#54B399"
},
"path": {
"stroke": "#54B399"
},
"rect": {
"fill": "#54B399"
},
"rule": {
"stroke": "#54B399"
},
"shape": {
"stroke": "#54B399"
},
"symbol": {
"fill": "#54B399"
},
"trail": {
"fill": "#54B399"
},
"title": {
"color": "#d4dae5"
},
"style": {
"guide-label": {
"fill": "#98a2b3"
},
"guide-title": {
"fill": "#d4dae5"
},
"group-title": {
"fill": "#d4dae5"
},
"group-subtitle": {
"fill": "#d4dae5"
}
},
"axis": {
"tickColor": "#343741",
"domainColor": "#343741",
"gridColor": "#343741"
},
"background": "transparent"
},
"width": "container",
"height": "container",
"autosize": {
"type": "fit",
"contains": "padding"
}
}
```
---
### Author:  [@Marco\_Liberati](https://discuss.elastic.co/u/Marco_Liberati)
#### Post date: [February 23, 2023, 1:51pm UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/9 "2023-02-23T13:51:19Z")
Maybe the filter is taking out the other entries? Try to disable it temporarily
---
### Author:  [@ankitdevnalkar](https://discuss.elastic.co/u/ankitdevnalkar)
#### Post date: [February 23, 2023, 1:57pm UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/10 "2023-02-23T13:57:07Z")
still same
---
### Author:  [@system](https://discuss.elastic.co/u/system)
#### Post date: [March 23, 2023, 1:57pm UTC](https://discuss.elastic.co/t/kibana-vega-sum-of-field-of-latest-unique-values/326047/11 "2023-03-23T13:57:25Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.