Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ZoeM
Specialist
Specialist

Only Unique Occurring Items to be listed

Hi Community,

I am probably overthinking this situation, but I need to figure out how to write an expression that will count ONLY the unique items that occurred Once with a value of 1.

In the attached QVW, I have 8 Items of which two occurred once each, Juice and Cream. I would like to only show these two items and I cant seem to figure how to do it.

 

any help rendered will be truly appreciated as always.

Labels (4)
1 Solution

Accepted Solutions
mrybalko
Creator II
Creator II

Hello 

Try this:

count({<Item = {"=aggr(count(Item), Item)=1"}> * <Value= {'1'}>} Item)

P.S. I recommend to precalculate special flags in load script. Aggr is heavy memory and cpu function.

View solution in original post

6 Replies
mrybalko
Creator II
Creator II

Hello 

Try this:

count({<Item = {"=aggr(count(Item), Item)=1"}> * <Value= {'1'}>} Item)

P.S. I recommend to precalculate special flags in load script. Aggr is heavy memory and cpu function.

ZoeM
Specialist
Specialist
Author

That did the trick, I will take a look at the expression you created and see if I can formulate it for my dashboard. 

Thanks Community

ZoeM
Specialist
Specialist
Author

What if...

Another dimension was thrown in, say region, and I want to see what item is still constantly at value 1? It would still be Cream and Juice, but I cant seem to manipulate your expression to capture this.

The result should be:

Cream and Juice perform consistently regardless of area.

mrybalko
Creator II
Creator II

You should add every new dimension in aggr function:

count({<Item = {"=aggr(count(Item), Item, Region)=1"}> * <Value= {'1'}>} Item)

Recommend this post and example

ZoeM
Specialist
Specialist
Author

Thanks for the suggestion.

However, I added another item called Mushroom and gave it a 1 value for NA, 2 value for EU and 3 value for SA. My result for the initial question i snow showing Cream, Juice and Mushroom. I should only have Cream and Juice, Mushroom had other values in other regions (2 in EU and 3 in SA). 

ZoeM
Specialist
Specialist
Author

forgot to attach the dashboard