Does Yavin support "speed" (average over date) measure?

[QubitPi]

Is your feature request related to a problem? Please describe.

I'm always frustrated when I see this problem blow

Describe the solution you'd like

As the title suggests, our team is having a Yavin app backed by Elide and we need to have some measure that report some business logic about how "fast" things go. For example, our database table have 3 columns

1. start_date (an example column value is 2023-06-15)
2. finish_date (2023-06-23)
3. total_amount (an example column value is 21)

Our speed is defined by

total_amount / (finish_date - start_date)

What we would like to see on a Yavin report is, for example, when we have 3 rows in the aforementioned table:

start_date	end_date	total_amount
2023-06-01	2023-06-02	10
2023-06-02	2023-06-05	90
2023-06-08	2023-06-10	30

then we can see some "trending" data like:

Our model configuration looks like this:

{
  tables: [
    {
      name: Speed Data
      friendlyName: Speed Data
      dbConnectionName: DBConnection
      dimensions: [
        {
          name: start_date
          friendlyName: Start Date
          category: Date
          type: TIME
          definition: '{{start_date}}'
          grains: [
            {
               type: DAY
            }
          ]
        }
        {
          name: finish_date
          friendlyName: Finish Date
          category: Date
          type: TIME
          definition: '{{$finish_date}}'
          grains: [
            {
               type: DAY
            }
          ]
        }
        {
          name: total_amount
          friendlyName: Total Amount
          category: Stats
          type: INTEGER
          definition: '{{$total_amount}}'
        }
      ]
      measures: [
        {
          name: speed
          friendlyName: How fast things go
          category: Stats
          type: INTEGER
          definition: '** we don't know yet, so we opened this issue **'
        }
      ]
    }
  ]
}

The closest analogy we can give is an average measure over some date range. However, we didn't find any documentations on definition for average.

Any suggestions would be greatly appreciated. Thanks.

Describe alternatives you've considered

I have no other idea, sorry.

Additional context

N/A

[jkusa]

Hi @QubitPi 👋 , Elide & Yavin supports all the functions your database supports (including aggregation). Here is an example from our demo config that uses sum, case, cast, and replace:

https://github.com/yavin-dev/framework/blob/27ac7ad2e670e595f517f6514639438411a1af40/packages/webservice/app/src/main/resources/demo-configs/models/tables/DefaultNamespace-Tables.hjson#L161

Not sure if you are looking to aggregate when calculating "speed", but this is an example I was able to create on our demo data without aggregation:

        {
          name: speed
          friendlyName: Speed
          category: Stats
          type: Decimal
          definition: '''
            (cast (case when {{$duration}} like '% min' then REPLACE({{$duration}}, ' min', '') else '0' end AS INT)) / ( NULLIF( DATEDIFF( YEAR, PARSEDATETIME({{$release_year}}, 'yyyy'), PARSEDATETIME({{$date_added}}, 'yyyy') ),  0) )
          '''
        }

This takes the duration of a movie in minutes and divides it by the difference in years between the year it was available on netflix and the movie release year.

[QubitPi]

Hi @jkusa, thank you very much for the prompt response. The movie example you provided really helped. With your hints, we can see the desired Yavin report with

        {
          name: speed
          friendlyName: Speed
          category: Stats
          type: Decimal
          definition: '''
            {{$total_amount}} / ( NULLIF( DATEDIFF( DAY, PARSEDATETIME({{$task_start_date}}, 'yyyy-mm-dd'), PARSEDATETIME({{$task_finish_date}}, 'yyyy-mm-dd') ),  0) )
          '''
        }

This helps our team a lot! We will dive into Yavin and Elide docs to learn more. Thanks again!

[QubitPi]

Issue resolved. Thanks