Aggregating 2 columns

[isciberras]

Hi,

I was wondering if anyone could help me out. I would like to create a count of values based on aggregating 2 columns.

The 3 fields i have are date, user and (Number of terminals logged on to). Using a straight table, if i place date and user as dimensions and in the expression create a count of the distinct (Number of terminals logged on to) I get my desired result which looks like the following:

Date	User	Count of  Terminals logged on to
01/10/2017	1	2
02/10/2017	2	1
03/10/2017	3	5

My query is:

I want to create a text box which displays a count if the number of terminals is greater than 1. However to do so I'm assuming i need to aggregate field Date and User and count the number terminals. I don't know how to do this, can anyone help please?

Than you very much,

Isaac

[Anil_Babu_Samineni]

Like this?

Sum(Aggr(Count({<[Count of Terminals logged on to] = {'>1'} >} [Count of Terminals logged on to]), Date, User))

OR If it is expression

Count({<[Count of Terminals logged on to] = {"=Expression > 1"} >} [Count of Terminals logged on to])

[isciberras]

Thanks Anil,

I tried your suggestions however unfortunately the first expression returned 0 and the second one doesn't seem to want to work. Just to confirm, in the second expression, where you wrote {"=Expression > 1"} am I meant to be writing: ({<[Count of Terminals logged on to] = {'>1'} >}  ?

Thanks,

Isaac

[Anil_Babu_Samineni]

If it is direct field then don't required {"=Exp...."} use simple {'>1'} then try

[#5]

Try this

sum({<[Count of  Terminals logged on to]= {"> 1"} >}[Count of  Terminals logged on to])

[sunny_talwar]

May be this

Sum(Aggr(If(CountExpressionHere > 1, 1, 0), Date, User))

[andrei_delta]

hello,

this is working for me: =Count( DISTINCT{<[Count of  Terminals logged on to] = {">1"} >} [Count of  Terminals logged on to])

Regards,

Andrei

[MohamedELH]

Dear,

I am blocked in an aggregation formula.

example:

invoice_id, customer_id, date, amount

123, abc, 01/01/2020, 500eur

525, def, 02/07/2020, 300eur

845, def, 15/02/2020, 100eur

874, abc, 01/02/2020, 300eur

I need to see for each row the avg per customer even if the result will be repeated in our case: 

it should look like :

123, abc, 400eur

525, def, 200eur

845, def, 200eur

874, abc, 400eur

I do =>  aggr(avg(amount),customer_id) but then it shows the result once for a customer and other rows of the same customer nothing is shown. I tried agg(avg(amount),customer_id,invoice_id) to have the result repeated but then no result at all is shown...

I miss something in the understanding of "aggr".

Kind Regards,

Mohamed.