# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Creator II

## How do I get Sum for each column?

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)

1 Solution

Accepted Solutions

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.

6 Replies

Hi,

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

can you try to separate the expressions and check that?

Creator II
Author

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.

You need to do

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Creator II
Author

Tried it, I get the wrong output.

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.

Creator II
Author

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