How do I get Sum for each column?

[haneeshmarella]

I get the sums for the columns per hourTotal Cartons, Total Pieces, but when I apply similar logic for Target Cartons, Target Pieces per hour, it gives me a wrong output.

The formula for each row per hour are

Total Cartons: Count(distinct CASE#)

Total Pieces: num(sum(UNITS#),'#,##0')

Target Cartons

num

(num

(if

(count(distinct CASE#)

>0,

HOURS/if(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)=1,1,

If(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)= HOURS,Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST),

(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)-1)))),0)*29,0)

Target Pieces

num

(num

(if

(sum(UNITS#)

>0,

HOURS/if(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)=1,1,

If(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)= HOURS,Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST),

(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)-1)))),0)*74,0)

[settu_periasamy]

Hi,

What would be the expected output for target cartons and target pieces?

can you try to separate the expressions and check that?

[haneeshmarella]

The Target Cartons and Target Pieces formula for each cell of each row per hour is the expression above. THe output would be the sum of the rows for each hour, for example, in the picture, under 10 PM, the target cartons would be 29 + 25.

Does that answer your question, Settu?

[Anil_Babu_Samineni]

You need to do

Sum(Aggr(Your expression, Dim1, Dim2))

[haneeshmarella]

Tried it, I get the wrong output.

[settu_periasamy · Accepted solution]

Can you try this?

sum(Aggr(

num(num(if(count(distinct CASE#)>0,

HOURS/if(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)=1,1,

If(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)= HOURS,Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST),

(Count(TOTAL <USER_NAME> DISTINCT DATE_TIME_CST)-1)))),0)*29,0)

,NAME,ASSOCIATE,DATETIME))

The highlighted fields should be same as your dimensions.

[haneeshmarella]

It works without the dimension of Associate. Thanks a lot Settu.