Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

The way to achieve your own success is the willingness to help somebody else. Go for it!

- Qlik Community
- :
- All Forums
- :
- QlikView App Dev
- :
- Re: Problem with Sum & Count avoiding a dimension

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Anonymous

Not applicable

2015-08-13
03:30 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

398 Views

1 Solution

Accepted Solutions

jonathandienst

Partner - Champion III

2015-08-13
03:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

274 Views

3 Replies

jonathandienst

Partner - Champion III

2015-08-13
03:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

275 Views

jafari_ervin

Creator III

2015-08-13
03:39 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Can you send sample qlikview file.

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

Anonymous

Not applicable

2015-08-13
03:50 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Have a nice day

274 Views