Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.,
Are you looking for something like this -
Exactaly it
I simply used a straight table with Sum(Traffic) and Count(RegID) as expressions.
I did it and I get the table aggregatin only the number of records per phone/date/traffic.
I am not sure if I understood what you said. Can you share an example?
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,
If you will use DISTINCT then you will always get 1 per RegID. Try without distinct.