topic Re: Aggregating 2 columns in QlikView

Aggregating 2 columns

isciberras — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregating 2 columns

Anil_Babu_Samineni — Fri, 13 Oct 2017 07:40:08 GMT

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

Re: Aggregating 2 columns

isciberras — Fri, 13 Oct 2017 09:40:26 GMT

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

Re: Aggregating 2 columns

Anil_Babu_Samineni — Fri, 13 Oct 2017 10:58:42 GMT

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

Re: Aggregating 2 columns

Fri, 13 Oct 2017 11:24:36 GMT

Try this

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

Re: Aggregating 2 columns

sunny_talwar — Fri, 13 Oct 2017 11:52:52 GMT

May be this

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

Re: Aggregating 2 columns

andrei_delta — Fri, 13 Oct 2017 14:37:40 GMT

hello,

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

Regards,

Andrei

Re: Aggregating 2 columns

MohamedELH — Tue, 11 Feb 2020 07:01:37 GMT

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.