Lens Metric - subtract a value based of number of days in query

[sqbell]

Hi,

I'm trying to create a metric that would show our profits. I'm using Lens Metric and the calculation so far is like so:

round((round((sum(money_amount_eur_reverse, kql='type:bet_transaction OR type:settle_transaction') * 0.75), 2) - sum(money_amount_eur, kql='type:bonus_transaction AND money_amount_eur > 0')) * 0.7, 2)

From this number I would like to subtract a static number that we pay each day. But I would also like it be multiplied by number of days selected in the dashboard's query. So if 24 hours are selected it would subtract x and if 30 days - 30 * x.

Is there any way to achieve this?

[flash1293 (Joe Reuter)]

It sounds what you actually want is this feature: [Lens] Time scaling without date histogram · Issue #79656 · elastic/kibana · GitHub

But unfortunately it's not implemented yet.

I can think of a workaround though which might work for you:

• Create a runtime field currentDay which outputs the current day per document (similar to Manipulate time using Elastic runtime fields from elastic content share) - e.g. for 2022-04-04 12:45:23 the runtime field would be a keyword 2022-04-04.
• Then, add the following to your formula: + unique_count(currentDay) * x

It will not work for partial days (e.g. on selecting half a day), but that might be just what you needx