Returning a Sum from a collection using IF

(0) Share

Report

Posted on by d3ell

276

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)

Categories:

Building Power Apps

I have the same question (0)

All responses (5)

Answers (1)

Sort by

wyotim 2,545 on at

Like
a
(0)

Report
Copy link

Link copied!
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

Was this reply helpful? Yes No
d3ell 276 on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
Verified answer

wyotim 2,545 on at

Like
a
(0)

Report
Copy link

Link copied!
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*

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No
d3ell 276 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you both for your replies.

Issue resolved now!

Was this reply helpful? Yes No