topic Re: Average and Sum of column in separate KPI text box? in QlikView

Average and Sum of column in separate KPI text box?

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

How do I get the average of Carton Efficiency and Pieces Efficiency in separate KPI box? Also, Sum of Total Cartons and Total Pieces in separate KPI box?

The formula for each row of the columns are:

Total Cartons : Count(distinct CASE#)

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

Carton Efficiency: num(Count(TOTAL <USER_NAME> DISTINCT CASE#)/(HOURS*29),'#.# %')

Pieces Efficiency: num(SUM(TOTAL <USER_NAME> DISTINCT UNITS#)/(HOURS*29),'#.# %')

Thank you.

Re: Average and Sum of column in separate KPI text box?

qlikviewwizard — Thu, 26 Jul 2018 04:32:29 GMT

Please upload sample file with the dummy data.

Re: Average and Sum of column in separate KPI text box?

settu_periasamy — Thu, 26 Jul 2018 04:32:40 GMT

Maybe try like this in the text box

Total Cartons : =SUM(aggr(Count(distinct CASE#),DATE,SUPERVISOR,ASSOCIATE))

Carton Efficiency :

=Avg(Aggr(

num(Count(TOTAL <USER_NAME> DISTINCT CASE#)/(HOURS*29),'#.# %')

,DATE,SUPERVISOR,ASSOCIATE))

Re: Average and Sum of column in separate KPI text box?

haneeshmarella — Thu, 26 Jul 2018 04:42:22 GMT

Thank you Settu. Can you help me here too please?

How do I get Sum for each column?

Re: Average and Sum of column in separate KPI text box?

haneeshmarella — Thu, 26 Jul 2018 04:42:48 GMT

Hi Arjun,

Settu has given me the answer. Thank you for the response.