Get the value from last day of the period

Hi all,

I have a Date model and another model that has the value for each day.

Date model

Date Month
01-01-2020 2020 Jan
01-02-2020 2020 Feb
02-02-2020 2020 Feb

Value model

Date Value
01-01-2020 6,145
31-01-2020 8,712
15-02-2020 9,732
28-02-2020 7,132

When creating report, I want get the last value for each month.

Month Value
2020 Jan 8,712
2020 Feb 7,132

At the moment, this is what I get

Month Value
2020 Jan (6,145 + 8,712) = 14,857
2020 Feb (9,732 + 7,132) = 16,864

Any idea on how should I achieve this. Thank you so much

1 Like

Hi @Ling,

This can be done with AQL where function. Check out our documentation (we have listed your use case as a sample usage as well :smile:): where | Holistics Docs

Specifically, you can create a semi-additive AQL metric that only gets the value of the last day. The metric expression would look something like this:

measure | where(date = max(date))

For example, you want to get the count of orders created in the last day of each month:

measure total_value_monthly {
  name: 'Total Value Monthly'
  type: 'number'
  definition: @aql Value  
    | count(Value.Value) 
    | where(Value.Date == max(Value.Date))

Hi @Ling,

You can also check out this detailed guide on how to use AQL to perform semi-additive calculation: Perform Semi-additive Calculation | Holistics Docs.

Hope this helps :love_you_gesture:


Thank you so much @Theodore_NT
The Semi-additive calculation is what I need :+1:

1 Like