Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

long5hot
New Contributor III

Set Expression Question

I'm still learning set expressions along with aggr, and find myself easily stumped by problems that I think should be easily solved.  Having spent the better part of a day on this one & getting nowhere, I thought it best to ask for help!

My purely fictitious test dataset is as follows:-

Shelves:

load * inline [

InventoryMonth,ShelfNumber,Fruit

2,1,Apple

2,1,Orange

2,1,Pear

2,2,Banana

3,1,Apple

3,1,Orange

3,2,Banana

3,3,Apple

3,2,Persimmon

3,2,Pomegranate

3,2,Passionfruit

4,1,Pear

];

The chart needs to display the count of shelves that had more than X types of fruit on them during the month.

Let's say X >= 2.  For month=2, only ShelfNumber 1 met that criteria, so the value on the chart should be 1.  For month=3, ShelfNumber 1 and 2 both had 2 or more types of fruit, so the chart would show 2.  Finally for month=4, the value would be zero.

I've tried numerous approaches to this, none of which seem to be close to working. I expected a simple solution such as the following to work...

count(distinct {<ShelfNumber = {'=count(distinct Fruit) >= 2'}>} ShelfNumber)

... however it returns values of 2, 2, 1 instead of the expected 1, 2, 0

Any assistance would be much appreciated!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Set Expression Question

You are not just checking the Count(DISTINCT Fruit) against ShelfNumber, but ShelfNumber and InventoryMonth, right? In that case, create a new field in the script

Shelves:

LOAD AutoNumber(InventoryMonth&ShelfNumber) as Key,

    *;

load * inline [

InventoryMonth,ShelfNumber,Fruit

2,1,Apple

2,1,Orange

2,1,Pear

2,2,Banana

3,1,Apple

3,1,Orange

3,2,Banana

3,3,Apple

3,2,Persimmon

3,2,Pomegranate

3,2,Passionfruit

4,1,Pear

];

and then try this

Count(DISTINCT {<Key = {"=Count(DISTINCT Fruit) >= 2"}>} Key)

5 Replies
MVP
MVP

Re: Set Expression Question

You are not just checking the Count(DISTINCT Fruit) against ShelfNumber, but ShelfNumber and InventoryMonth, right? In that case, create a new field in the script

Shelves:

LOAD AutoNumber(InventoryMonth&ShelfNumber) as Key,

    *;

load * inline [

InventoryMonth,ShelfNumber,Fruit

2,1,Apple

2,1,Orange

2,1,Pear

2,2,Banana

3,1,Apple

3,1,Orange

3,2,Banana

3,3,Apple

3,2,Persimmon

3,2,Pomegranate

3,2,Passionfruit

4,1,Pear

];

and then try this

Count(DISTINCT {<Key = {"=Count(DISTINCT Fruit) >= 2"}>} Key)

MVP
MVP

Re: Set Expression Question

Alternatively, you can use aggr() function with both ShelfNumber and InventoryNumber to do this

=Count(DISTINCT Aggr(If(Count(DISTINCT Fruit) >= 2, ShelfNumber), ShelfNumber, InventoryMonth))


Capture.PNG

long5hot
New Contributor III

Re: Set Expression Question

Brilliant, thankyou!

long5hot
New Contributor III

Re: Set Expression Question

Yes, that would work as well. I was trying to avoid the use of an IF statement though, as the actual data I'm working with has a huge number of records.

MVP
MVP

Re: Set Expression Question

Yup, I would go with set analysis any day over the use of Aggr() and if()