topic Re: How do I get Sum for each column? in QlikView

How do I get Sum for each column?

haneeshmarella — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: How do I get Sum for each column?

settu_periasamy — Thu, 26 Jul 2018 05:04:42 GMT

Hi,

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

can you try to separate the expressions and check that?

Re: How do I get Sum for each column?

haneeshmarella — Thu, 26 Jul 2018 05:19:29 GMT

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?

Re: How do I get Sum for each column?

Anil_Babu_Samineni — Thu, 26 Jul 2018 05:27:00 GMT

You need to do

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

Re: How do I get Sum for each column?

haneeshmarella — Thu, 26 Jul 2018 05:31:34 GMT

Tried it, I get the wrong output.

Re: How do I get Sum for each column?

settu_periasamy — Thu, 26 Jul 2018 05:40:03 GMT

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.

Re: How do I get Sum for each column?

haneeshmarella — Thu, 26 Jul 2018 13:47:20 GMT

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