Returning a Sum from a collection using IF

[d3ell]

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)

[wyotim]

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]

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 · Accepted solution]

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]

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,

[d3ell]

Thank you both for your replies.

Issue resolved now!