Displaying the most recent data in graph

Our product publishes posts to social media (Facebook, Twitter etc) and I'm trying to produce some graphs to analyse our data. What I'd like to show some graphs on how many posts publishes successfully vs how many fail to publish. At first I created created a pie chart with using the 'unique count' metric on the id field and split the slice using result.keyword field which is either 'failure' or 'success'. The problem with this is that a post can attempt to publish up to 3 times. So if a post only publishes on attempt 3 it's going be included in both the 'failure' segment and the 'success' segment of the chart'. Is there a way to count each post once?

Here is an example of my data for 1 post attempting to publish:

{
postID: 'abc-1',
channel: 'Facebook',
date: 2021-11-12T10:00:00.100Z,
severity: error,
message: 'post failed to published reason xyz'
result: 'failure'
}
{
postID: 'abc-1',
channel: 'Facebook',
date: 2021-11-12T10:00:00.200Z,
severity: error,
message: 'post failed to published reason xyz'
result: 'failure'
}
{
postID: 'abc-1',
channel: 'Facebook',
dateStamp: 2021-11-12T10:00:00.500Z,
severity: info,
message: 'post successfully published'
result: 'success'
}
{
postID: 'abc-2',
channel: 'Twitter',
date: 2021-11-12T10:30:00.000Z,
severity: info,
message: 'post successfully published'
result: 'success'
}
{
postID: 'abc-3',
channel: 'Facebook',
date: 2021-11-12T10:35:30.250Z,
severity: error,
message: 'post failed to published reason xyz'
result: 'failure'
}
{
postID: 'abc-3',
channel: 'Facebook',
dateStamp: 2021-11-12T10:35:30.500Z,
severity: info,
message: 'post successfully published'
result: 'success'
}

What about adding the time window in your query?

Here is a similar question and some suggestions including excellent suggestion from our lens expert using latest value aggregation.

1 Like

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