Struggling with defining my expression in a bar chart

[Anonymous]

My x axis is calendar month

My y axis is resource effort

my data contains many columns but the ones I am trying to use are:

resource effort whose values are either 1 or 0

resource name whose value is the resource name

i want to sum the resource effort, but i need it to be by distinct name, since "resource name" has many of the same names, just summing up resource effort won't give me the correct data unless i account for each name. otherwise the number is too high

I want to define my expression to be sum of resource effort where resource name is distinct. is this possible? I get "garbage after..." when ever i try to add anything after Sum(resource effort)

[johnw · Accepted solution]

Unclear exactly what you're after when you're talking about "by distinct name". Perhaps one of these?

sum(aggr(max([resource effort]),[resource name]))

count({<[resource effort]={1}>} distinct [resource name])

[Anonymous]

Hey, thanks for the reply. Allow me to elaborate. I need to sum up the resource effort column, but i can't just add it up to give me 5, because John Smith is listed 3 times. So I need to add up ResourceEffort by the distinct names, in the ResouceName Column, to get a total of 3. (only need to count john smith once). I guess I stink at sql. The expression sum(ResourceEffort) won't work for my purpose. Not sure

ResourceName Column         ResourceEffort Column

john smith                                1

alex jones                                1

tom davis                                 0

michael jordan                         1

john smith                                1

chris rock                                 0

john smith                                1

[johnw]

I think either of the expressions above would give you what you want, then. Although they'd produce the same result, they have different flavors that could correspond to the subtleties of meaning. One is adding up resource efforts in a particular way, one is counting distinct resource names in a particular way. Since resource effort is always 1 or 0 they should return the same thing, but to me they imply different things about the data and about what you're doing with it.

[Anonymous]

Thank you John, I used the 2nd expression and got what I needed.

[Anonymous]

John, where is a good resource I can use to learn expressions like you have provided me? Thanks.

sum(aggr(max([resource effort]),[resource name]))

count({<[resource effort]={1}>} distinct [resource name])

[johnw]

For set analysis, I learned mostly from the help text - there are a number of examples in there. And of course with experience and with reading the forum.

For aggr(), I think I understood it from the first time I saw it, though the more examples I see the better I understand what all can be done with it.

So I'm not very good at suggesting resources, sorry! I'm sure people have suggested resources before, though, if you search the forum.