topic Re: AGGREGATE FUNCTION IN EXPRESSION AGGR() in QlikView

AGGREGATE FUNCTION IN EXPRESSION AGGR()

Anonymous — Wed, 15 Aug 2018 19:12:02 GMT

To All,

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

Based in the column beloow:

RegID	Date	Phone	Traffic
1	1/Jul/2018	21 911111111	2000
2	1/Jul/2018	21 911111111	3000
3	2/Jul/2018	21 911111111	4000
4	1/Jul/2018	21 92222222	3000
5	1/Jul/2018	21 92222222	4000
6	2/Jul/2018	21 92222222	2000
7	3Jul//2018	21 92222222	3000
8	4/Jul/2018	21 93333333	2000

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:

Date	Phone	Total traffic	# RegID
1/Jul/2018	21 911111111	5000	2
1/Jul/2018	21 92222222	7000	2
2/Jul/2018	21 911111111	4000	1

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.,

Re: AGGREGATE FUNCTION IN EXPRESSION AGGR()

neelamsaroha157 — Wed, 15 Aug 2018 19:19:21 GMT

Are you looking for something like this -

Re: AGGREGATE FUNCTION IN EXPRESSION AGGR()

Anonymous — Wed, 15 Aug 2018 19:37:57 GMT

Exactaly it

Re: AGGREGATE FUNCTION IN EXPRESSION AGGR()

neelamsaroha157 — Wed, 15 Aug 2018 19:39:59 GMT

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

Re: AGGREGATE FUNCTION IN EXPRESSION AGGR()

Anonymous — Wed, 15 Aug 2018 19:53:55 GMT

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

Re: AGGREGATE FUNCTION IN EXPRESSION AGGR()

neelamsaroha157 — Wed, 15 Aug 2018 20:42:13 GMT

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

Re: AGGREGATE FUNCTION IN EXPRESSION AGGR()

Anonymous — Thu, 16 Aug 2018 00:14:05 GMT

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.

Data	MSISDN	=Sum(GGSN_Tráfego)	=Count(DISTINCT RegID)
21/07/2018	21995677753	300	1
21/07/2018	21995677753	310	1
21/07/2018	21997186622	280	1
21/07/2018	21997186622	280	1
21/07/2018	21997186622	280	1

2) In a Linear table with an AGGR()

Data	MSISDN	= Sum(Aggr(Sum(GGSN_Tráfego), Data, MSISDN))	=Count(Aggr(Count(DISTINCT RegID), Data, MSISDN))
		890	3
21/07/2018	21995677753	300	1
21/07/2018	21995677753	310	1
21/07/2018	21997186622	280	1
21/07/2018	21997186622	280	1
21/07/2018	21997186622	280	1

Bellow is What I expet as a result:

Data	MSISDN	Tráego TOTAL	# RegID
21/07/2018	21995677753	610	2
21/07/2018	21997186622	840	3

Thansk again.

Rgds,

Re: AGGREGATE FUNCTION IN EXPRESSION AGGR()

neelamsaroha157 — Thu, 16 Aug 2018 12:53:19 GMT

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