um and filtering

[alexandre-sabot]

Hello community,

I'm desperately trying to make a simple request, or so I thought.

From a view, I want to add up the amount over a year, see below:

sum( select(Revenue book[Amount], number(year(date(Revenue book[Accounting year]))) = 2024) )

But the query doesn't return anything. Without the filter on the date, it works correctly except that I have everything. [Accounting year] is a virtual column in which I have extracted only the year of type number.

If you have an idea ?

Thanks

[Suvrutt_Gurjar · Accepted solution]

Please try

sum( select(Revenue book[Amount], YEAR([Accounting year]) = 2024) )

The above assumes the [Accounting year] is a date type column.

If it is number type, then , please try

sum( select(Revenue book[Amount], [Accounting year] = 2024) )

If it is text type, please try

sum( select(Revenue book[Amount], NUMBER([Accounting year]) = 2024) )

Please note that such multi row expressions can be sync time expensive,  especially if used in a virtual column.

You may want to evaluate " group by" sum option in summary views instead or creating a slice depending on your use case.

[dntrplytch]

 You could try (removing the table name before the column [Accounting year]):

sum( select(Revenue book[Amount], number(year(date([Accounting year]))) = 2024) )

[Suvrutt_Gurjar · Accepted solution]

Please try

sum( select(Revenue book[Amount], YEAR([Accounting year]) = 2024) )

The above assumes the [Accounting year] is a date type column.

If it is number type, then , please try

sum( select(Revenue book[Amount], [Accounting year] = 2024) )

If it is text type, please try

sum( select(Revenue book[Amount], NUMBER([Accounting year]) = 2024) )

Please note that such multi row expressions can be sync time expensive,  especially if used in a virtual column.

You may want to evaluate " group by" sum option in summary views instead or creating a slice depending on your use case.

[Denzil_Snyman]

Perhaps have a look at  what you get with year(date([Accounting Year])) on its own as type number, I have seen this return 2,024 on many occasions. Like mentioned above, the type column and expressions have a big impact on result. 

[Peterson_90]

sum( select(Revenue book[Amount],[Accounting year] = 2024) ))

If it doesn't return anything, it means that your "Accounting year" column doesn't have a number or something like this.

I saw that you used the name of a table in your formula, this is not necessary to refer to the table again in the select formula. 

[alexandre-sabot]

Thank you for all your responses.

I think it's the fact that I prefixed the column with the table name that is messing it up, because everything I used now works.

With this query, it works

sum(
select(Livre des recettes[Montant], [Année comptable] = [_THISROW].[Année])
)

On the other hand, now I wanted to multiply with the quantity and it no longer works

sum(
select(Livre des recettes[Montant]*Livre des recettes[Quantité], [Année comptable] = [_THISROW].[Année])
)

Unable to find column 'Année comptable'

With prefix table

sum(
select(Livre des recettes[Montant]*Livre des recettes[Quantité], Livre des recettes[Année comptable] = [_THISROW].[Année])
)

Cannot compare List with Number in (Livre des recettes[Année comptable] = [_THISROW].[Année])

Thank you

[alexandre-sabot]

I saw this, but it doesn't work, unable to find column

SUM(SELECT(generic_table_name[column_a*column_b];[id_column]=[_thisrow].[id_column]))

[Suvrutt_Gurjar · Accepted solution]

AppSheet syntax does not work that way for the multiplication within a SELECT(0 statement.

Assuming you wish to multiple [Amount] and [Quantity] for each record before summing, please create another column called say [Total] with an expression something like [Montant]*[Quantité]

Please note if it is a physical column it will update for existing records only when the record is edited. Alternatively you could create a virtual column for [Total]

Then your sum expression can be something like 

sum(
select(Livre des recettes[Total],[Année comptable] = [_THISROW].[Année])
)

[alexandre-sabot]

Thank you. I was thinking about it, but I told myself that there was a syntax