Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Talk to Experts Tuesday, January 26th at 10AM EST: Qlik Sense. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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

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

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

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

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.

View solution in original post

Creator II
Creator II

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