Dataview GROUP BY doesn't work with Choice Function

Help
1

Hello everybody :slight_smile:

I have a small problem concerning a group by in my Dataview Query.
I have a bunch of Notes in the “Finance/Spending” Folder.
All Notes in there have the Properties:

  • Cost::
  • Active::
  • Interval::

Interval can contain the Values “Monthly” or a number from 1 to 12 if the spending only occurs in a specific month.

Now I tried to write a Query that would sum all my spendings for individual months in a column.
I started with the January Column which would look like this:

TABLE
sum(
    choice(
        Interval = "Monthly", rows.Cost, choice(
            contains(Interval, 1), rows.cost, 0)
        )
    ) as January
FROM "Finance/Spending"
WHERE Active = true
GROUP BY Active

The problem is that I only get 0 as the Final Sum.
I then tried to simplify the Query to see where exactly the problem is.
If I leave out the group by I get a list of all my spending with the correct costs, if I leave out the choice I can create a sum.

So my guess here is that the group by needs the “rows.” to recognize the array to sum. But because of the choice function, I can’t tell it that the result is the array to sum :frowning:

It would be great if somebody could help me with this. I’m still new to Dataview.
Lots of Thanks :slight_smile:

2

It’s not entirely clear how and where your problem and/or use case. So could you provide some more information as to how your data looks. Like how the table without the group by looks? Or similar.

If you don’t do the GROUP BY, you don’t have access to any rows object, since that rows object is created when you group, and then it contains all fields which were available within that group.

So it could be that your problem is related to you having lists of Cost within each file, which when grouped becomes a list of lists, and you’ll need to do something like sum(flat(rows.Cost)).

Furthermore, using GROUP BY Active with the requirement of all files having Active set to true in effect makes your table has just one line, but I reckon that’s good?

Finally, do either of your inline fields repeat in any of your source documents? Or if that is common, do they ever be the only element in that file for that field?

3

Hi thanks for the Reply,
I’ll try to do a better job explaning the issue.

As a Result I would like a table that looks like:

January Fabuary March and so on
100$ 200$ 120$ xxx$

The code I posted is just January (the rest would be just copied code with the difference in the interval number).

I have all my Spendings in individual notes. Think of a note with title Netflix and Properties:
Interval:: Monthly
Active:. true
Cost:: 10

If I had a yearly Netflix Invocie that was due in January it would look like this:
Interval:: 1
Active:. true
Cost:: 10

Without the GROUP BY the Querry Looks like this:

TABLE
    choice(
        Interval = "Monthly", Cost, choice(
            contains(Interval, 1),cost, 0)
        ) as January
FROM "Finance/Spending"
WHERE Active = true

this returns the correct cost for all my spending so all monthly spendings have a cost and all january spendings have a cost. spendings from feburay (Interval:: 2) are listed as 0.

If I ignore the choice function like this:

TABLE
sum(rows.Cost) as January
FROM "Finance/Spending"
WHERE Active = true
GROUP BY Active

I get a Sum but I lose the Interval Conditions. Since I want different conditions for every Column I can’t just use the WHERE Statement.

So it could be that your problem is related to you having lists of Cost within each file, which when grouped becomes a list of lists, and you’ll need to do something like sum(flat(rows.Cost))

The Costs only contain one Number per Note.

Furthermore, using GROUP BY Active with the requirement of all files having Active set to true in effect makes your table has just one line, but I reckon that’s good?

That is true and what I’m aiming for. This way I will have the Months as column headers and only the summed up spending as the only row.

I don’t know if I understand this correctly (sry english is not my first language) but all the selected notes have the same properties and every property has only one information in them.

I hope this makes my question easier to understand.

and thanks for the reply

4

Choosing to have the months as columns instead of rows, makes this query a lot harder to do. It can be done, but it’s more complicated, as you need to gather all the cost values into the table, and then filter out each of them for each month.

```dataview
TABLE WITHOUT ID
  sum( map( 
      filter(rows, (r) => r.Interval = 1 or r.Interval = "Monthly"),
      (m) => m.Cost )) as January,
  sum( map( 
      filter(rows, (r) => r.Interval = 2 or r.Interval = "Monthly"),
      (m) => m.Cost )) as February
FROM "Finance/Spending"
WHERE Active
GROUP BY true
```

This is untested, but hopefully it should do the magic. In order to have something to sum we first filter() out the rows having either Monthly or 1 in the Interval field, and then we use that row to pick out the Cost field.

1 Like
5

Oh Wow this works like a charm :slight_smile:
Thank you so much for your help.

6

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