Show last record in a time range

Ask the Community
1

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
2

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:

3

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.

4

Hi @hari,

My apologies as I’ve missed your latest response. I do hope you have found a solution to this case!

In case this is still an active problem, I would love to follow up on it.

Firstly, I’d love the clarify the above solution, in case my previous answer might be confusing:

  • The Timestamp_int integer should be set very big (e.g. Date_timestamp*10000000000). Then, we will sum it with the actual Closing balance to make a surrogate value (let’s call it cus_dim_a).

    • For example:
      | Date | Timestamp_int | Closing_balance | cus_dim_a
      | 20230412 | 202304120000000000 | 125 | 202304120000000125
      | 20230427 | 202304270000000000 | 62 | 202304270000000062

  • This way, the Closing balance is very small compared to cus_dim_a and will not change cus_dim_a’s order. So we can easily find the latest day, by finding the biggest cus_dim_a value in the filtered time range.

  • Finally, we can create a Business calculation field to calculate the Closing balance of that day.
    End_of_period_value = max(cus_dim_a) - max(timestamp_int)

May I check if this is the ideal result for you?
Please let me know if I missed anything. Cheers! :beers: