cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
d3ell
Helper V
Helper V

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
wyotim
Resident Rockstar
Resident Rockstar

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
wyotim
Resident Rockstar
Resident Rockstar

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

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?
wyotim
Resident Rockstar
Resident Rockstar

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*

v-xida-msft
Community Support
Community Support

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
Helper V
Helper V

Thank you both for your replies.

 

Issue resolved now! Smiley Happy

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,198)