Hey all, I'm trying to build a metric and am strug...
# gooddata-cloud
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.
Copy code
select LAST_VALUE(({fact/reporting_building.size}))
ORDER BY {label/report_date.day} ASC 
Any help/insight/pointers would be appreciated, thanks!
Hi Sidd, One question here please. As per our documentation:
Copy code
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
values for a single
values because there are multiple
Building ID
for each
. 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
I have tried it with your sample data and metric definition:
Copy code
select LAST_VALUE(SUM({fact/size}))
ORDER BY {label/report_date.day} ASC
and here is the result if I break this metric down by attributes Quarter/Year and Building ID:
it should display last value for each day,month, week, quarter or year depending on what granularity you select for Date in “group by”
it will display last sum of all building sizes in case I remove Building Id from Columns:
you can use
if you want to have breakdown by Month and display last value of the whole quarter in each month
Other approach can be to define order of buildings for calculation of the last value. In this case aggregation is not needed:
Copy code
select LAST_VALUE({fact/size})
ORDER BY {label/report_date.day} ASC, {label/building_id} ASC
@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:
LAST_VALUE ({fact/size}) ORDER BY {label/report_date.day}
WITHIN ({label/building_id})
(BTW to make the
function work at all, it first required me to set a primary key on the dataset. I set report_date + buildingID)
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!! 🙏
just following up, I used the first metric you gave Jakub, but also needed to update my primary key like Michal mentioned and it is now working! thanks again for the help!
🤝 2
🙌 2
🙌🏼 1