I'm attempting to forecast warranty renewal costs for hardware using a TSVB on Kibana 6.5.1.
My use case is to provide a dashboard where someone can select the end-date (start date should be today), and depending on when they select as the end-date, they'll see the cost to renew warranties until that date.
I basically just need a way to get the difference in time between some arbitrary date in the future and a time field in ES. Once I have the time difference per doc (or average), I can convert that to a monetary cost.
My current approach is to take the timestamp of the last bucket and then subtract the warranty-end timestamp for each document from that, however I'm unsure of how to access the timestamp of the last bucket in a TSVB, or elsewhere for that matter.
What I've tried:
Subtracting warranty end date from end of bucket timestamp
- Summing the positive differences in time (end of time-scope > warranty end date) and multiplying that by a known monetary cost per day:
sum(((clamp(max(params._timestamp) - mean(params._all.devices.timestamps), 0, 1000000000000000000) ) / (1000 * 86400 * 30.4375) ) * params.devices * 10)
Fails
Because params._timestamp
returns the end timestamp of the bucket containing the document, therefore the math is all based on the bucket interval. E.g. in the screenshot above, the interval auto-adjusted to 7 days. So the costs shown above are the cost to renew expiring warranties when they expire, but only renew the warranty until the end of the bucket (at most, 7 days).
I've also tried to obtain the maximum value for params._timestamp using max(params._timestamp)
and subsequently aggregate the 'Overall Maximum' of this Math aggregation, however it appears that Math aggregations can not be an input to another aggregation:
I know this is kind of a non-standard use case of Kibana, but if I can get it to work, it unlocks a whole bunch of other forecasting I'd like to do..
What Works (Kind of):
Manual interval update to force all documents to share the same bucket (& thus the same params._timestamp)
I can manually update the interval to the entire range, but that's not something I can reasonably expect another user to do. This appears to give me the correct results, but I lose the ability for users to generate their own queries: