I feel like I must be missing something obvious, since I feel I'm trying to do something relatively simple and can't find a way to make it work.
I have one table with rows that contain 2 columns of interest; hours, and an enum field that lets the user pick from a list of 3 text values [type1, type2, type3] that describe the 'type' of hours.
I have another table that has 3 columns in which I'd like to add all hour totals for each of the three types. I saw somewhere else I'd probably have to use a combination of select (to get the hour values for the pertinent rows) and sum (to add them), which seems fine.
The problem I'm having is I have not been able to get the values I'm interested in. All the select statements I've tried either display an error, or end up returning a list of *all* hour values.
The two main variations on expressions I've tried are
Select(leave[Hours], IN(type1, leave[type])) (which just seems to see if 'type1' is in the list of *possible* enum values, and always returns true - thus returning *all* hour values
Select(leave[hours],leave[type] = 'type1) (which gives an error: Cannot compare Text with List)
So my question is, how do I do a comparison against the selected value of an enum column for a specific row rather than checking against the full list of possible enum values?
Solved! Go to Solution.
Welcome to the community!
You should be using something like:
SUM( SELECT(leave[Hours], [type] = [_ThisRow].[type]) )
Welcome to the community!
You should be using something like:
SUM( SELECT(leave[Hours], [type] = [_ThisRow].[type]) )
I think my main mistake was in referencing using the table name in the second argument to the select rather than just the column name. Once I changed "Select(leave[hours],leave[type] = 'type1) " to "Select(leave[hours], [type] = 'type1') " all is good. (of course I then was able to add some additional criteria and move on to SUM). I also added an additional ref column for additional filtering.
My final expression looked something like this:
SUM(SELECT(leave[hours], AND( IN( [_rownumber], [_thisrow].[related_leave]),[type]=type1, [Person]=[_thisrow].[person])))
Thanks so much, it was driving me a bit mad!
I'm glad you have a solution.
Would you please explain the purpose of this part?
IN( [_rownumber], [_thisrow].[related_leave])
I doubt it is giving you the desired result.
User | Count |
---|---|
37 | |
30 | |
29 | |
22 | |
18 |