topic Re: Set Analysis: use aggregation over combination of dimensions as condition in App Development

Set Analysis: use aggregation over combination of dimensions as condition

Kellerassel — Fri, 26 May 2023 05:53:48 GMT

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?

Re: Set Analysis: use aggregation over combination of dimensions as condition

canerkan — Thu, 25 May 2023 15:20:02 GMT

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

Re: Set Analysis: use aggregation over combination of dimensions as condition

Kellerassel — Thu, 25 May 2023 17:12:35 GMT

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.

Re: Set Analysis: use aggregation over combination of dimensions as condition

Kellerassel — Thu, 25 May 2023 19:19:51 GMT

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.

Re: Set Analysis: use aggregation over combination of dimensions as condition

Kushal_Chawda — Fri, 26 May 2023 00:04:10 GMT

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

Re: Set Analysis: use aggregation over combination of dimensions as condition

Kellerassel — Fri, 26 May 2023 05:56:34 GMT

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.

Re: Set Analysis: use aggregation over combination of dimensions as condition

Kushal_Chawda — Fri, 26 May 2023 10:36:31 GMT

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