Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

AGGREGATE FUNCTION IN EXPRESSION AGGR()

To All,

I´m new here and in Qlikview world. Thanks in advance !

Based in the column beloow:

RegIDDatePhoneTraffic
11/Jul/201821 9111111112000
21/Jul/201821 9111111113000
32/Jul/201821 9111111114000
41/Jul/201821 922222223000
51/Jul/201821 922222224000
62/Jul/201821 922222222000
73Jul//201821 922222223000
84/Jul/201821 933333332000

I need to generate an analisys that aggregate the total traffic per phone number per date and add a column with the number of aggregated records like the example bellow:

DatePhoneTotal traffic# RegID
1/Jul/201821 91111111150002
1/Jul/201821 9222222270002
2/Jul/201821 91111111140001

Please consider the aggregation string suggestion in the body of the response, unfortunatelly my environment does not permit to use a external qvw file to view the solutions.

 

Rgds.,

7 Replies
neelamsaroha157
Specialist II
Specialist II

Are you looking for something like this -

Capture12.PNG

Anonymous
Not applicable
Author

Exactaly it

neelamsaroha157
Specialist II
Specialist II

I simply used a straight table with Sum(Traffic) and Count(RegID) as expressions.

Anonymous
Not applicable
Author

I did it and I get the table aggregatin only the number of records per phone/date/traffic.

neelamsaroha157
Specialist II
Specialist II

I am not sure if I understood what you said. Can you share an example?

Anonymous
Not applicable
Author

Hi Neelam,

I tried to do a simple SUM and COUNT and it now works as I want. See bellow the results and what I expect

1) in a Pivot Table.

  

DataMSISDN=Sum(GGSN_Tráfego)=Count(DISTINCT RegID)
21/07/2018219956777533001
21/07/2018219956777533101
21/07/2018219971866222801
21/07/2018219971866222801
21/07/2018219971866222801

2) In a Linear table with an AGGR()

 

DataMSISDN= Sum(Aggr(Sum(GGSN_Tráfego), Data, MSISDN))=Count(Aggr(Count(DISTINCT RegID), Data, MSISDN))
8903
21/07/2018219956777533001
21/07/2018219956777533101
21/07/2018219971866222801
21/07/2018219971866222801
21/07/2018219971866222801

Bellow is What I expet as a result:

  

DataMSISDNTráego TOTAL# RegID
21/07/2018219956777536102
21/07/2018219971866228403

Thansk again.

Rgds,

neelamsaroha157
Specialist II
Specialist II

If you will use DISTINCT then you will always get 1 per RegID. Try without distinct.