Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Problem with Sum & Count avoiding a dimension

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

1 Solution

Accepted Solutions
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
3 Replies
Partner - Champion III

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Creator III

Can you send sample qlikview file.

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

Anonymous
Not applicable
Author

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

Have a nice day

Community Browser