How to sum up result of a column and remove the users?

[haneeshmarella]

How do I sum up OFFLINE Ops at Rate result of each column and remove USER_NAME such that I get the number 3 (1+1+1) under OFFLINE Ops at Rate for Supervisor?

OFFLINE Ops at Rate is

IF(Count(DISTINCT IF(OFF_ON_LINE='OFF-LINE',CASE#))>=[OFF-LINE

Hours]*70,count(distinct IF(OFF_ON_LINE='OFF-LINE',USER_NAME)),0) for each USER_NAME name.

I used the below, but it gives a result of 0.

sum(aggr(IF(Count(DISTINCT IF(OFF_ON_LINE='OFF-LINE',CASE#))>=[OFF-LINE

Hours]*70,count(distinct IF(OFF_ON_LINE='OFF-LINE',USER_NAME)),0), USER_NAME,SUPERVISOR,DATE))

[sunny_talwar]

What is

[OFF-LINE

Hours] in your expression? Is this a field? variable? or expression label?

[haneeshmarella]

It is an expression label.

=

sum(aggr(IF(OFF_ON_LINE='OFF-LINE',HOURS),OFF_ON_LINE,DATE,USER_NAME))

[sunny_talwar]

That is what I thought it was... you cannot use expression labels within Aggr() function... may be use the expression itself

Sum(Aggr(If(Count(DISTINCT If(OFF_ON_LINE = 'OFF-LINE', CASE#)) >= Sum(Aggr(If(OFF_ON_LINE = 'OFF-LINE', HOURS), OFF_ON_LINE, DATE, USER_NAME))*70, Count(DISTINCT If(OFF_ON_LINE = 'OFF-LINE', USER_NAME)), 0), USER_NAME, SUPERVISOR, DATE))

[sunny_talwar]

Or try this

Sum(Aggr(If(Count(DISTINCT {<OFF_ON_LINE = {'OFF-LINE'}>} CASE#) >= Sum(Aggr(If(OFF_ON_LINE = 'OFF-LINE', HOURS), OFF_ON_LINE, DATE, USER_NAME))*70, Count(DISTINCT{<OFF_ON_LINE = {'OFF-LINE'}>} USER_NAME), 0), USER_NAME, SUPERVISOR, DATE))

[haneeshmarella]

Wow, I didn't know we could not do that. Where do I find small pointers like these to learn?

In a way it gives me a right answer, but I would like a distinct number as a USER worked 3 times but I want it as 1. When I use Sum(DISTINCT aggr...), the below image becomes 1 instead of 6. Any suggestion on what to change?

[haneeshmarella]

Gives the same result as above, but easier than IF condition for complex expressions in future.

But how do I get the distinct as the screenshots below?

[sunny_talwar]

May be try this

Count({<OFF_ON_LINE = {'OFF-LINE'}>} DISTINCT Aggr(If(Count(DISTINCT {<OFF_ON_LINE = {'OFF-LINE'}>} CASE#) >= Sum(Aggr(If(OFF_ON_LINE = 'OFF-LINE', HOURS), OFF_ON_LINE, DATE, USER_NAME))*70, USER_NAME), USER_NAME, SUPERVISOR, DATE))

[haneeshmarella]

Gives me wrong result. What is happening is it gives me 4, but the right answer is 3. It adds 1 to a person who does not meet the condition (Nemat is the USER, if you see the difference from the screenshot on original question, it changed when I replaced label with original expression).

[haneeshmarella]