Set Analysis: use aggregation over combination of dimensions as condition

[Kellerassel]

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?

[canerkan]

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:

Transactions_by_employee:

Load

Count(Distinct Transactions) as CountTransaction,

Employee,

Date

From Transactions

Group by Employee, Date;


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

Let me know if that helped

Regards

Can

[Kellerassel]

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.

[Kellerassel]

 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.

[Kushal_Chawda]

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

[Kellerassel]

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. 

[Kushal_Chawda]

@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)

or

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