Hi, I have a data set that looks like this,
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 131, 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 131, 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
Hi @hari ,
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 PointInTime metric.
 For this, we’ll convert the
Date
timestamp into an integer, which we will sum with theClosing 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.
 Do note that this integer has to be big enough, so after summing with
 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 Endofperiod value should do it.
We’ve put together a detailed Howto doc: How to Calculate Dynamic PointInTime 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
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.
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
 For example:

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!