Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Multiple conditional counts in Set Analysis

I am trying to count the instances where there is a buy indication and a sell indication for an item from two different customers. Same customer can have buy & sell indication in which case it doesn’t count.

This is what I could think of:

IF(

(count({$<Direction = {'SELL'}>} distinct customer_id) > 0) // there is a seller

AND

(count({$<Direction = {'BUY'}>} distinct customer_id) > 0) //there is a buy indication from a different customer

AND

(count({$<Direction = {'BUY', 'SELL'}>} distinct customer_id) > 1) // i.e if number of distinct customers with buy OR sell is also greater than one then it is a match

, 1, 0)

Resulting in the following table

Item, BuySellFlag

A       1

B       0

C       1

D       0

This works but I can’t plot this on time series chart because I always get 1 if the condition is satisfied. Also the totals in the table is also always 1

Is the way to do count( {<the condition above>} item_id) ? Would AGGR help in this case?

I am using QlikSense.

Thanks!

Tags (1)
1 Solution

Accepted Solutions
MVP
MVP

Re: Multiple conditional counts in Set Analysis

Set Analysis itself will not consider the chart dimensions, since it's evaluated only once and not in the context of the dimension.

You could maybe create a key made of item_id and month in your script, then use this key instead of item_id in your set modifier.

7 Replies

Re: Multiple conditional counts in Set Analysis

May be this:

Sum(Aggr(

If(

(count({$<Direction = {'SELL'}>} distinct customer_id) > 0) // there is a seller

AND

(count({$<Direction = {'BUY'}>} distinct customer_id) > 0) //there is a buy indication from a different customer

AND

(count({$<Direction = {'BUY', 'SELL'}>} distinct customer_id) > 1) // i.e if number of distinct customers with buy OR sell is also greater than one then it is a match

, 1, 0), YourChartDimensionsHere))

Replace YourChartDimensionsHere with the chart dimensions separated by commas

Re: Multiple conditional counts in Set Analysis

My bad, try this instead:

Sum(Aggr(

If(

(count({$<Direction = {'SELL'}>} distinct customer_id) > 0) // there is a seller

AND

(count({$<Direction = {'BUY'}>} distinct customer_id) > 0) //there is a buy indication from a different customer

AND

(count({$<Direction = {'BUY', 'SELL'}>} distinct customer_id) > 1) // i.e if number of distinct customers with buy OR sell is also greater than one then it is a match

, 1, 0), item_id))

MVP
MVP

Re: Multiple conditional counts in Set Analysis

Questions about set analysis are hard to answer without knowing the sets and the relation between the field that define your sets (AKA data model).

Just guessing:

=Count(

{<item_id = p({<Direction = {'SELL'}>) * p({<Direction = {'BUY'}>}) * {"=count({$<Direction = {'BUY', 'SELL'}>} distinct customer_id) > 1)"} >}

DISTINCT item_id)

Not applicable

Re: Multiple conditional counts in Set Analysis

Thanks! This worked;Just had to fix a couple of parenthesis

Count({<item_id = p({<Direction  = {'SELL'}>}) * p({<Direction  = {'BUY'}>}) * {"=count({$<Direction  = {'BUY', 'SELL'}>} distinct customer_id) > 1"} >} DISTINCT item_id)

but it seems to ignore the date dimension (I have applied a month filter April 2016) and is considering all records up until that day.

Clip41.png

the average match per day is actually 17

MVP
MVP

Re: Multiple conditional counts in Set Analysis

Set Analysis itself will not consider the chart dimensions, since it's evaluated only once and not in the context of the dimension.

You could maybe create a key made of item_id and month in your script, then use this key instead of item_id in your set modifier.

Not applicable

Re: Multiple conditional counts in Set Analysis

Added (item_id & date) as item_id_date in the load script


then changed the script to


Count({<item_id_date = p({<Direction  = {'SELL'}>}) * p({<Direction  = {'BUY'}>}) * {"=count({$<Direction  = {'BUY', 'SELL'}>} distinct customer_id) > 1"} >} DISTINCT item_id)


This gives the following chart. This exactly what I wanted 

Clip42.png


Also found this interesting discussion on the topic Evaluating "sets" in the context of a dimension


Thanks again!

Not applicable

Re: Multiple conditional counts in Set Analysis

This approach works as well; Had to add (item_id & date) AS item_id_date in the script and then use

Sum(Aggr(

If(

(count({$<Direction = {'SELL'}>} distinct customer_id) > 0) // there is a seller

AND

(count({$<Direction = {'BUY'}>} distinct customer_id) > 0) //there is a buy indication from a different customer

AND

(count({$<Direction = {'BUY', 'SELL'}>} distinct customer_id) > 1) // i.e if number of distinct customers with buy OR sell is also greater than one then it is a match

, 1, 0), item_id_date))


Thanks a lot!

Community Browser