cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
d3ell
Level: Powered On

Returning a Sum from a collection using IF

Hi,

 

I have a collection which for ease at minute has the follwing columns DateCalc which is the date formatted as "yyyymmdd" , hours and job

 

I wanting to have a label on a different screen which shows me the total hours  from the collection based on the DateCalc Column.

 

The collection has various dates in it and i'm only wanting to return the sum of all the items where the date matches a hidden label.

 

For example (if today) i have a label on the screen which would be "20190516" i would want to return the SUM of the hours column for all the records in the collection where the DateCalc column has 20190516 in it not any others

 

I can return the total value of all the hours in the collection by using Sum(JobCollection,hours) however i'm unable to work out how to do this when filtering.  i have tried the below and various others. any thoughts?

Sum(Filter(JobCollection,DateCalc=labeltoday.text),hours)
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Returning a Sum from a collection using IF

One quick correction on my part: case-sensitivity matters in PowerApps, so I should have put

Sum(
    Filter(
        JobCollection,
        DateCalc = Value(labeltoday.Text),
    ),
    hours
)

If hours is actually Hours or labeltoday is LabelToday, etc., that will matter and would also cause your formula to fail. Perhaps check that first when you try it out, just to be sure.

 

The easiest way to check (that I know anyway) is from the blue squiggly errors that pop up when trying to compare two values, like in your Filter statement. If there is a blue squiggle under the equals sign, that usually indicates either a type error or a delegation error. Putting the mouse cursor over the equals sign should show the error message, which would be something like "Incompatible type" and some more info. 

 

As far as changing the column type in a collection, that is something I haven't ever tried. Usually, I would just handle it by changing the type of the value that is being compared (in your case, like I did by wrapping a Value function around the labeltoday.Text item). I will fiddle with changing a collection tonight and see what I can come up with though.

 

*edit for spelling*

View solution in original post

5 REPLIES 5
Super User
Super User

Re: Returning a Sum from a collection using IF

The syntax looks right for your Sum/Filter so I am wondering if it your DateCalc is stored as a number. Maybe try this:

 

Sum(
    Filter(
        JobCollection,
        DateCalc = Value(labeltoday.text),
    ),
    hours
)

 Otherwise, I would check to make sure what is stored in hours are also numbers. If it is text, the Sum function won't like that. Let me know if that doesn't do the trick and I'll be happy to try and see what else might be happening

d3ell
Level: Powered On

Re: Returning a Sum from a collection using IF

Thanks for your reply, I’m out of the office so can’t try till tomorrow now, but will try your suggestion as I’m not sure what the values have been saved as, is there a way to check/change the column type in a collection?
Super User
Super User

Re: Returning a Sum from a collection using IF

One quick correction on my part: case-sensitivity matters in PowerApps, so I should have put

Sum(
    Filter(
        JobCollection,
        DateCalc = Value(labeltoday.Text),
    ),
    hours
)

If hours is actually Hours or labeltoday is LabelToday, etc., that will matter and would also cause your formula to fail. Perhaps check that first when you try it out, just to be sure.

 

The easiest way to check (that I know anyway) is from the blue squiggly errors that pop up when trying to compare two values, like in your Filter statement. If there is a blue squiggle under the equals sign, that usually indicates either a type error or a delegation error. Putting the mouse cursor over the equals sign should show the error message, which would be something like "Incompatible type" and some more info. 

 

As far as changing the column type in a collection, that is something I haven't ever tried. Usually, I would just handle it by changing the type of the value that is being compared (in your case, like I did by wrapping a Value function around the labeltoday.Text item). I will fiddle with changing a collection tonight and see what I can come up with though.

 

*edit for spelling*

View solution in original post

Community Support Team
Community Support Team

Re: Returning a Sum from a collection using IF

Hi @d3ell ,

Could you please share a bit more about your scenario?

Which type value do you store in the DateCalc column of your Collection? Number value or Text value?

 

Please consider modify your formula as below:

Sum(
Filter(
JobCollection,
Text(DateCalc) = labeltoday.text
),
hours
)

Or

Sum(
   Filter(
          JobCollection,
          Value(DateCalc) = Value(labeltoday.text)
   ),
   hours
)

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
d3ell
Level: Powered On

Re: Returning a Sum from a collection using IF

Thank you both for your replies.

 

Issue resolved now! Smiley Happy

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,179)