topic Problem with Sum & Count avoiding a dimension in QlikView

Problem with Sum & Count avoiding a dimension

Anonymous — Thu, 13 Aug 2015 07:30:34 GMT

Hi everybody, hope somebody can help me. I'll try to explain what I want to achieve. I have a data set with the following structure:

What I need, is a pivot table that allows to do this:

I'm using the following expression to get the number of Total orders per employee regardless warehouse

AGGR(Count(OrderN), Employee)

And the following to the number of orders with weight between 20-50 kg per employee regardless warehouse

Count(if((aggr(sum(kg),OrderN)>=20)AND (aggr(sum(kg),OrderN)<=50),1))

but I get this result

Shouldn't repeat the values in the two rows? Maybe this can´t be done.

Hope someone can enligthen me.

Regards

jonathandienst — Thu, 13 Aug 2015 07:37:31 GMT

The aggr() functions must include all the dimensions in the table:

=Count(if((aggr(sum(kg), OrderN, ....)>=20)AND (aggr(sum(kg), OrderN, .....)<=50),1))

For example:

=Count(if((aggr(sum(kg), OrderN, WAREHOUSE)>=20)AND (aggr(sum(kg), OrderN, WAREHOUSE)<=50),1))

jafari_ervin — Thu, 13 Aug 2015 07:39:00 GMT

Can you send sample qlikview file.

I think you handle this problem with total <Dimension> keyword.

Anonymous — Thu, 13 Aug 2015 07:50:58 GMT

Thank you both for your time. The solution is the combination of both answers.

Have a nice day