Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
cancel
Showing results for 
Search instead for 
Did you mean: 
haneeshmarella
Creator II
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.

SS.PNG

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
settu_periasamy

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.

View solution in original post

6 Replies
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
Creator II
Creator II

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

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)
haneeshmarella
Creator II
Creator II

Tried it, I get the wrong output.

settu_periasamy

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.

View solution in original post

haneeshmarella
Creator II
Creator II

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