Skip to main content

App Development

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

Skip the ticket, Chat with Qlik Support instead for instant assistance.
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Set Analysis: use aggregation over combination of dimensions as condition

I have a set expression that works:


Min({<employee = {"=Count(Distinct transaction) > 1"}>} date)



The example is hypothetical:

A data set of transactions by employees in departments. A Table with department as dimension.

The above formula should give me for each department the earliest date of a transaction made by an employee  that has overall completed more than one transaction. Right?

But I would like to have the earliest date an employee has completed at least two transactions on the same day. 

So Count(Distinct transaction) > 1 has to be true for a combination of employee and date for the employee to be the selected by the set expression. 

ChatGPT has funnily enough suggested this solution wich is rubbish, but it illustrates what I'm looking for:


Min({<employee & date = {"=Count(Distinct transaction) > 1"}>} date)



How can this be done?



Labels (1)
6 Replies
Partner - Creator II
Partner - Creator II

Hi Kellerassel,

you can achieve this in your Data Load Editor. To give you an Idea, the script could look like this. You just have to adjust the Table and Fieldnames:




Count(Distinct Transactions) as CountTransaction,



From Transactions

Group by Employee, Date;

Min(Date) as FirstDate,
From Transactions_by_employee
Where CountTransaction > 1
Group by Employee;


Let me know if that helped




Contributor III
Contributor III

Thanks for you response! However I can't adjust the skript for that, so I am looking for a solution with chart functions, and out of a special interest a solution with set expressions.

Contributor III
Contributor III

 I have a formula:

Date(Aggr(Min(Aggr(Min({<date = {"=Count(DISTINCT transaction) > 1"}>} date), department, employee)), department))

But this only works if I filter one or more departments from the table.

Without filtering, just department as a table dimension, it gives me a wrong date. I can see the date output flip when selecting and deselecting a department. I'm very confused.


@Kellerassel  Data example will be essential here to give you correct answer. 

Contributor III
Contributor III

Hi @Kushal_Chawda , I can't share company data, but I was able to create a reproducible example with sample data. I have attached a qvf to this question. You will be able to understand my problem with this data. 

Example: Department 5 shows me a wrong date in the table, as soon as I select department 5 it shows me the correct value. But this doesn't go for all cases. See for yourself. 


@Kellerassel  if you are going to use set expression, you need primary key to evaluate your condition in set. I have assumed that transaction_id is your primary_key.If it is not primary key, you can create it using RowNo() function in script. Other option is to use if condition instead if you are not able create primary key in script

=min({< transaction_id = {"=aggr(Count(distinct transaction_id), department,employee_id,date)>1"}>}date)


=min(aggr(if(Count(distinct transaction_id)>1, date), department,employee_id,date))