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]

Don't know...

[sunny_talwar]

Actually won't know unless you can share a sample

[haneeshmarella]

Please find the attached. Let me know if you have any questions.

[sunny_talwar]

One thing to do is to create WeekStart(DATE) in the script... because DATE and WeekStart(DATE)... may or may not be equal... you are using DATE in Aggr() and WeekStart(DATE) in the expression... Just create it in the script and use that in the Aggr() as well

[sunny_talwar]

Beside, if I remove USER_NAME dimension.. I see 3 for the first row... isn't that what you wanted?

[haneeshmarella]

If you look at the original question, the total OFFLINE operators were 4. The resultant OFFLINE Ops at Rate should be 3 (in the same screenshot). With formulas I used (only aggr) I got them for OFFLINE Ops at Rate ---

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

When I remove USER_NAME for above, I get 0.

But when I replace it with your formula, or use sum on the above aggr, I get a different result as below and its wrong as Nemat must be 0 (sorry changed the column names as I lost the previous one) but it gives 1 and changes entire result.

[sunny_talwar · Accepted solution]

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

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

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

[haneeshmarella]

Took about 45 mins to correct and add WEEKSTART, but works great. Thank you Sunny.

[sunny_talwar]

Took about 45 mins to correct and add WEEKSTART

How come? I did make the update in the attached qvw file... did you not see it or was there another issue?

[haneeshmarella]

I just gave you a sample file by taking the sheet out of my main QVW.

I then ran the original script on a day i.e about 20 days ago by adding WEEKSTART to it for creating the QVD. And then did an incremental load for the last 20 days into the QVD.

Is there a way to make the tables/charts calculate faster, as it takes a while to run the formulas under the expressions?