Show last record in a time range

Hi, I have a data set that looks like this,
hol
I am adding a widget which displays the value in the 2nd column in the image for each date.
But if I am choosing a range of date, suppose May 1-31, I want to show the latest record which is against the date may 30 in my data set. How can I achieve this.
Consider the value column as closing balance for a day. for eg: my closing balance for may 1st is 100$ and on May 30th is 50$. so when I select the date range of may 1-31, the closing balance should be 50$ ideally .
currently am adding the value field as sum(value_column) and this returns the sum of all values in that range. But I want to fetch only the latest record

1 Like

Hi @hari :raising_hand_man:t2:,

This is a very interesting case, and I’d love to support you with it.

My understanding is that: when filtering a date range from A to B (B is after A), the widget will return the value of the last date with a record between A and B. Please correct me if I missed anything.

If yes then it’s a bit tricky at the moment, since Holistics doesn’t support window functions like LAST_VALUE in calculation field just yet.


A solution (or a hack, more like) to this: a dynamic Point-In-Time metric.

  • For this, we’ll convert the Date timestamp into an integer, which we will sum with the Closing balance value → to create a surrogate value.
    • Do note that this integer has to be big enough, so after summing with Closing balance value it won’t affect the order.
  • Then, we’ll use this surr_value integer to look for the last day (which is now the maximum value possible) in the period.
  • After that, a Business calculation to calculate the End-of-period value should do it.

We’ve put together a detailed How-to doc: How to Calculate Dynamic Point-In-Time Metrics in Holistics | Holistics Docs.
The example in this doc is not exactly the same as your case, but it’s quite close and can make a good reference.

Please try this out, and let me know should you have more questions. Cheers :pray:t2:

Hi @vu.duong ,
Thanks for the response.
The closing balance on a day need not be always greater than the previous day. This is more like outstanding payment from a customer. so lets say a guy owes me 100$ today, the closing balance will be 100 and if he pays me 20$ the next day, the closing balance will be 80$ tomorrow.so the surrogate value when sorted in increasing order, need not always give me the value on last day at the last row.