Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

haneeshmarella
Contributor II

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

SS.PNG

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))

Tags (1)
1 Solution

Accepted Solutions

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

Like I said, it seems to be related to the WeekStart dimension...

I created WeekStart in the script... and see what I got

Capture.PNG

Dimension

WeekStart

SUPERVISOR

Expression

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, WeekStart))

View solution in original post

21 Replies

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

What is

[OFF-LINE

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

haneeshmarella
Contributor II

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

It is an expression label.

=

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

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

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))

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

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
Contributor II

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

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

SS.PNG

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?

SS.PNG

haneeshmarella
Contributor II

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

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?

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

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
Contributor II

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

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
Contributor II

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

SS.PNG

SS1.PNG