Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
isciberras
Contributor

Aggregating 2 columns

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/201712
02/10/201721
03/10/201735

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

6 Replies

Re: Aggregating 2 columns

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
isciberras
Contributor

Re: Aggregating 2 columns

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

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable

Re: Aggregating 2 columns

Try this

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

Re: Aggregating 2 columns

May be this

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

Partner
Partner

Re: Aggregating 2 columns

hello,

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

Regards,

Andrei