Hey all, I'm trying to build a metric and am struggling to find a solution and I'd appreciate some help. I have a time series table with a daily snapshot of the size of each building. Sample table: | report_date | Building ID | Size | |----------------|-------------|------| | 2024-03-30 | 1 | 300 | | 2024-03-31 | 1 | 301 | | 2024-03-30 | 2 | 81 | | 2024-03-31 | 2 | 100 | I want to report on trending data, for each quarter. However, I don't want to do a simple aggregation of the Size value such as sum, avg, etc., but rather, I want to see the size value from the last date of that quarter for each building. So this is what I'd imagine the table output to look like. | Quarter/Year| Building ID | Size | |----------------|--------------|------| | 2024-Q1 | 1 | 301 | | | 2 | 100 | Ideally, if the output table was grouped by Year, the metric would then show the size for each building at the end of each shown year, but if a different metric needs to be created for each grouping that is fine as well. I tried using the LAST_VALUE function to create a quarter specific metric, but am getting 0 as the value.

select LAST_VALUE(({fact/reporting_building.size}))
ORDER BY {label/report_date.day} ASC 
WITHIN({label/report_date.quarter})

Any help/insight/pointers would be appreciated, thanks!

Hi Sidd, One question here please. As per our documentation:

If you use facts, make sure that the fact has only a single value defined for each ORDER BY attribute.

Could you please confirm if you comply with this requirement and every report_date.day has only one fact (size) value assigned?

hm, so that could be the issue. there are multiple

size

values for a single

report_date

values because there are multiple

Building ID

for each

report_date

. is there are an alternative approach?

I think you just need to add some aggregation to the formula how to reduce multiple values to a single value in case the metric will be not sliced by

building ID

.

@Jakub Sterba will that initial metric (with SUM before LAST_VALUE) work also without BuildingID in the insight? I think it might break if some building ID is added or removed over time… This one seems to work for me even without building in the insight - it first applies LAST_VALUE per buildingID (+any date from the insight) and then the SUM:

SELECT SUM(

LAST_VALUE ({fact/size}) ORDER BY {label/report_date.day}

WITHIN ({label/building_id})

)

yes, LAST_VALUE is very flexible, and different approaches will deliver slightly different results in edge cases. • The first metric I was proposing was returning sum value of snapshots for the last day so buildings which are not included in that day will be not included. • The second metric (with two arguments of ORDER BY) will return size of the last building in the last day of snapshots • metric by Michal will find last value in any day of period for each building and then sum them up. It may include buildings which were not in the last snapshot It is a question what is the wanted behavior and if cases where building ID is not present in report need to be covered or not.

Thanks @Jakub Sterba and @Michal Hauzírek! will try these suggestions and get back to you. Appreciate the help!! 🙏