Average last 12 entrys

What I’m trying to do

I try to calculate my average income from a Database with the Rows “Income” and “Date”

Things I have tried

This is how far i got

Table without ID Income
From "DB/Income"
Sort Date desc
Limit 12
flatten average(Income)

but the output are the 12 entrys in my database, not an average of them

To get access to the average() & co function, you’ll need to group your query, and that moves all values into a rows object, so here is an untested variant, which hopefully do want you want it to do:

```dataview
TABLE average(rows.Income) as "Avg. Income"
FROM "DB/Income"
SORT Date desc
GROUP BY Date
LIMIT 12
```

This should, in theory, sort your stuff by date, group on any given date, and then limit the output to the last 12 dates, and show the average of the income on those days.

Update: Corrected type of rows.Income

i also tried it like you said but all i get is a list with 12 empty entrys. all of them are just dashes. no error message :frowning:

i suspect the “rows.” does not work properly for me
When i type in “row.” it outputs the 12 values (also no average)

Do you get anything with either of these queries? (PS! I originally wrote rows.income, not rows.Income in the previous post, that might be a bad thing… )

```dataview
TABLE length(rows)
FROM "DB/Income"
SORT Date desc
GROUP BY Date
```

or 

```dataview
TABLE WITHOUT ID Date, Income
FROM "DB/Income"
SORT Date desc
LIMIT 20
```

Could you report back either result?

the first output is

Date(28) length(rows)
- 2
2021-10-31 1
2021-11-30 1
2021-12-31 1
2022-01-31 1
2022-02-28 1
2022-03-31 1
2022-04-30 1
2022-05-31 1
2022-06-30 1
2022-07-31 1
2022-08-31 1
.
.

and the second:

Date(20) Income
2023-12-31 4213.83
2023-11-30 4249.65
2023-10-31 2765.24
2023-09-30 2765.24
2023-08-31 2765.24
2023-07-31 1365
2023-06-30 1277.74
2023-05-31 1101.5
2023-04-30 4026

Then my original query, at least with the proper casing of rows.Income should show the correct result…

```dataview
TABLE average(rows.Income) as "Avg. Income"
FROM "DB/Income"
SORT Date desc
WHERE Date
GROUP BY Date
LIMIT 12
```

This variant also removes those two entries not having a proper Date set.

output (again) ist just the Values as list.
also now the sorting seems broken. desc and asc displays the exact same months

Date(12) Avg. Income
2021-10-31 2166
2021-11-30 3420
2021-12-31 1941
2022-01-31 2048.7
2022-02-28 2008.7
2022-03-31 4572
2022-04-30 2008.7
2022-05-31 2127
2022-06-30 4444.48
2022-07-31 2167.42
2022-08-31 2107.26
2022-09-30 2148.3

i fixed the sorting issue by changing the order

TABLE average(rows.Income) as "Avg. Income"
FROM "DB/Income"
GROUP BY Date
SORT Date desc
LIMIT 12
WHERE Date

but still no average

Ehh… Do you want the average for each date, or for all dates in your query? If for all dates, then why the LIMIT 12 ?

Or if you just want for the last 12 income reports you’ve got, that’s another query. Then you need to switch around the order, and rather do something like:

```dataview
TABLE average(rows.Income) as "Avg. Income"
FROM "DB/Income"
SORT Date desc
WHERE Date
LIMIT 12
GROUP BY true
```

So please specify what your end result is supposed to be, so I know we’re on the same page here. The query I presented before assumed multiple incomes for each date, and therefore grouped them together…

i want the average for the last 12 Months
your last code worked. thanks a lot!

1 Like

Sorry for misreading your request, but you did in fact get the average in my original query… It just was the wrong average of a single entry… :smiley:

Glad we finally got on the same page, and we could get it working!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.