Sum and filter collection

[SilvermarD]

Hello

I am trying to sum a column in a collection based on the user and the month for a sales team.

The collection is colTotalSale and the column to be summed is TotalSale.  

Sum(Filter(colTotalSale, Date=Month(Now()), User=User().FullName), TotalSale, thought this would work but sadly not.  Probably spent too much time on it and cant work out what the error is.  

Could some fresh eyes point out the obvious to me 🙂

Thanks

[Adrian_Celis]

Hi @SilvermarD ,

If you are going to filter by month then you have to use a range. Something like:

Filter(colTotalSale, Date >= Date(Year(Now()),Month(Now()),1), Date <= DateAdd(Date(Year(Now()),Month(Now()),1),1,"Months"), User=User().FullName)

[SilvermarD]

Thanks Adrian.

This is throwing up an error, saying the property on this control expects text values.  Tried to make the date range work but cant seem to get it right.

[Adrian_Celis]

Hi @SilvermarD 

Can you show me the formula that you've done so far and what is the error saying exactly?

[SilvermarD]

Hello Adrian

Thanks for getting back to me.

I tried the filter you sent me but adding in Sale total as this is the information I am trying to extract.  The error was Unexpected characters, formula contains 'ParenClose' where Eof is expected.  Also, invalid argument type (table).  Expecting a number value instead.

Had a look at how to do date ranges and came up with below, however the error with this one is invalid argument type (table).  Expecting a number value instead.

Sum(Filter(colTotalSale, DateDiff("1/1/22", Now()) < 0 && DateDiff(Now(), "31/1/22") > 0), User, Sale_Total)

Thanks

[Adrian_Celis]

Hi @SilvermarD 

If the error is 'ParentClose' then most likely it's missing a parentesis. So what is the 'User' in your formula? Is it filtering out the user? If so then it should be something like:

Sum(Filter(colTotalSale, DateDiff("1/1/22", Now()) < 0 && DateDiff(Now(), "31/1/22") > 0, User = "Some Name"), Sale_Total)

Let me know if it works. If not then we'll investigate deeper.

[SilvermarD · Accepted solution]

Fantastic, no errors with the formula.  Unfortunately, its not producing a result, so something is a miss somewhere.

Many thanks

[Adrian_Celis]

Hi @SilvermarD 

Ok firstly it will be good if you take it one step at a time. You should determine if your table result is producing any data at all.

I am not sure about if your filter formula will work but essentially it should look something like this:

Filter(colTotalSale, YourDateColumnName >= Date(2022,1,1) && YourDateColumnName <= Date(2022,2,1))

The formula above should filter out all items on your date column for the month of Jan 2022.