Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2016-09-15 04:40 AM

How to perform a calculation on top 3 values of a group?

Hi,

i have a chart that displays data as following:

Region

Country

Mobile Network Operator

3rd party service provider

I'm using a group to drill down in the order mentioned above.

For each of the above i have success rate that is calculated from as following:

count( {<Result_AGG= {SUCCESS,WARNING} >} Result_AGG ) / count( {<Result_AGG = {FAILURE_MESSAGE_NOT_RECEIVED,FAILURE_MESSAGE_WRONG_CONTENT,FAILURE_MESSAGE_WRONG_TYPE,FAILURE_NO_RESULT,SUCCESS,WARNING} >} Result_AGG )

Eventually i'm only using the top 3 service providers so i'd like to calculate my success rate according to the top 3 results and i want to to be displayed through all of my states (per region, per country etc..)

I've tried to use "=if(aggr(rank(" according to this thread Calculating TOP 10 VALUES in PIVOT TABLE but my problem seems to be the group i'm using.


Can someone please help?


Thanks!

466 Views
0 Likes
3 Replies
sunny_talwar
MVP
MVP
‎2016-09-15 01:29 PM

Would you be able to provide a sample with the expected output to help you better here?

373 Views
0 Likes
Not applicable
‎2016-09-19 04:30 AM
Author

Hi,

Attaching short example table.

I'd like to get top 3 providers for each country/network/region.

A success for me is a result with Success/Warning.

      

Service ProviderCOUNTRYNETWORKREGIONResultdate
AmdTelecomAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-25
AmdTelecomAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-26
AmdTelecomAustraliaSingtel OptusAsia & PacificWARNING2016-07-25
AmdTelecomAustraliaSingtel OptusAsia & PacificWARNING2016-07-26
AmdTelecomAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-07-26
AmdTelecomAustraliaTelstra Corp. Ltd.Asia & PacificWARNING2016-07-26
AmdTelecomAustraliaVodafoneAsia & PacificSUCCESS2016-07-25
AmdTelecomAustraliaVodafoneAsia & PacificSUCCESS2016-08-02
AmdTelecomAustraliaVodafoneAsia & PacificWARNING2016-07-25
AmdTelecomAustraliaVodafoneAsia & PacificWARNING2016-08-02
BulkAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-25
BulkAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-26
BulkAustraliaSingtel OptusAsia & PacificWARNING2016-07-25
BulkAustraliaSingtel OptusAsia & PacificWARNING2016-07-26
BulkAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-07-26
BulkAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-09-14
BulkAustraliaTelstra Corp. Ltd.Asia & PacificWARNING2016-07-26
BulkAustraliaTelstra Corp. Ltd.Asia & PacificWARNING2016-09-14
BulkAustraliaVodafoneAsia & PacificSUCCESS2016-07-25
BulkAustraliaVodafoneAsia & PacificSUCCESS2016-08-02
BulkAustraliaVodafoneAsia & PacificWARNING2016-07-25
BulkAustraliaVodafoneAsia & PacificWARNING2016-08-02
CardboardFishAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-25
CardboardFishAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-26
CardboardFishAustraliaTelstra Corp. Ltd.Asia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-26
CardboardFishAustraliaTelstra Corp. Ltd.Asia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-09-14
CardboardFishAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-07-26
CardboardFishAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-09-14
CardboardFishAustraliaVodafoneAsia & PacificSUCCESS2016-07-25
CardboardFishAustraliaVodafoneAsia & PacificSUCCESS2016-08-02
ClickatellAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-25
ClickatellAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-26
ClickatellAustraliaSingtel OptusAsia & PacificWARNING2016-07-25
ClickatellAustraliaSingtel OptusAsia & PacificWARNING2016-07-26
ClickatellAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-07-26
ClickatellAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-09-14
ClickatellAustraliaTelstra Corp. Ltd.Asia & PacificWARNING2016-07-26
ClickatellAustraliaTelstra Corp. Ltd.Asia & PacificWARNING2016-09-14
ClickatellAustraliaVodafoneAsia & PacificSUCCESS2016-07-25
ClickatellAustraliaVodafoneAsia & PacificSUCCESS2016-08-02
ClickatellAustraliaVodafoneAsia & PacificWARNING2016-07-25
ClickatellAustraliaVodafoneAsia & PacificWARNING2016-08-02
CMSMSProvider"}AustraliaSingtel OptusAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-25
CMSMSProvider"}AustraliaSingtel OptusAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-26
CMSMSProvider"}AustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-07-26
CMSMSProvider"}AustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-09-14
CMSMSProvider"}AustraliaVodafoneAsia & PacificSUCCESS2016-07-25
CMSMSProvider"}AustraliaVodafoneAsia & PacificSUCCESS2016-08-02
MyCoolSMSProvider"}AustraliaSingtel OptusAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-25
MyCoolSMSProvider"}AustraliaSingtel OptusAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-26
MyCoolSMSProvider"}AustraliaSingtel OptusAsia & PacificWARNING2016-07-25
MyCoolSMSProvider"}AustraliaSingtel OptusAsia & PacificWARNING2016-07-26
MyCoolSMSProvider"}AustraliaTelstra Corp. Ltd.Asia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-26
MyCoolSMSProvider"}AustraliaTelstra Corp. Ltd.Asia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-09-14
MyCoolSMSProvider"}AustraliaVodafoneAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-25
MyCoolSMSProvider"}AustraliaVodafoneAsia & PacificWARNING2016-08-02
NexmoAustraliaSingtel OptusAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-25
NexmoAustraliaSingtel OptusAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-26
NexmoAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-25
NexmoAustraliaSingtel OptusAsia & PacificSUCCESS2016-07-26
NexmoAustraliaSingtel OptusAsia & PacificWARNING2016-07-25
NexmoAustraliaSingtel OptusAsia & PacificWARNING2016-07-26
NexmoAustraliaTelstra Corp. Ltd.Asia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-26
NexmoAustraliaTelstra Corp. Ltd.Asia & PacificSUCCESS2016-07-26
NexmoAustraliaTelstra Corp. Ltd.Asia & PacificWARNING2016-07-26
NexmoAustraliaVodafoneAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-07-25
NexmoAustraliaVodafoneAsia & PacificFAILURE_MESSAGE_NOT_RECEIVED2016-08-02
NexmoAustraliaVodafoneAsia & PacificSUCCESS2016-07-25
NexmoAustraliaVodafoneAsia & PacificSUCCESS2016-08-02
NexmoAustraliaVodafoneAsia & PacificWARNING2016-07-25
NexmoAustraliaVodafoneAsia & PacificWARNING2016-08-02
AmdTelecomAustria3/Orange/One ConnectEuropeSUCCESS2016-04-19
AmdTelecomAustria3/Orange/One ConnectEuropeWARNING2016-04-19
AmdTelecomAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-04
AmdTelecomAustriaA1 MobilKomEuropeSUCCESS2016-04-14
AmdTelecomAustriaA1 MobilKomEuropeSUCCESS2016-07-04
AmdTelecomAustriaA1 MobilKomEuropeWARNING2016-04-14
AmdTelecomAustriaH3GEuropeSUCCESS2016-04-29
AmdTelecomAustriaH3GEuropeWARNING2016-04-29
AmdTelecomAustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-06
AmdTelecomAustriaT-Mobile/TeleringEuropeSUCCESS2016-04-14
AmdTelecomAustriaT-Mobile/TeleringEuropeSUCCESS2016-07-06
AmdTelecomAustriaT-Mobile/TeleringEuropeWARNING2016-04-14
BulkAustria3/Orange/One ConnectEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-19
BulkAustria3/Orange/One ConnectEuropeSUCCESS2016-04-19
BulkAustria3/Orange/One ConnectEuropeWARNING2016-04-19
BulkAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-14
BulkAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-04
BulkAustriaA1 MobilKomEuropeSUCCESS2016-04-14
BulkAustriaA1 MobilKomEuropeSUCCESS2016-07-04
BulkAustriaA1 MobilKomEuropeWARNING2016-04-14
BulkAustriaA1 MobilKomEuropeWARNING2016-07-04
BulkAustriaH3GEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-29
BulkAustriaH3GEuropeSUCCESS2016-04-29
BulkAustriaH3GEuropeWARNING2016-04-29
BulkAustriaT-Mobile/TeleringEuropeSUCCESS2016-04-14
BulkAustriaT-Mobile/TeleringEuropeSUCCESS2016-07-06
BulkAustriaT-Mobile/TeleringEuropeWARNING2016-04-14
BulkAustriaT-Mobile/TeleringEuropeWARNING2016-07-06
CardboardFishAustria3/Orange/One ConnectEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-19
CardboardFishAustria3/Orange/One ConnectEuropeSUCCESS2016-04-19
CardboardFishAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-14
CardboardFishAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-04
CardboardFishAustriaA1 MobilKomEuropeSUCCESS2016-04-14
CardboardFishAustriaA1 MobilKomEuropeSUCCESS2016-07-04
CardboardFishAustriaH3GEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-29
CardboardFishAustriaH3GEuropeSUCCESS2016-04-29
CardboardFishAustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-14
CardboardFishAustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-06
CardboardFishAustriaT-Mobile/TeleringEuropeSUCCESS2016-04-14
CardboardFishAustriaT-Mobile/TeleringEuropeSUCCESS2016-07-06
ClickatellAustria3/Orange/One ConnectEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-19
ClickatellAustria3/Orange/One ConnectEuropeSUCCESS2016-04-19
ClickatellAustria3/Orange/One ConnectEuropeWARNING2016-04-19
ClickatellAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-14
ClickatellAustriaA1 MobilKomEuropeSUCCESS2016-04-14
ClickatellAustriaA1 MobilKomEuropeSUCCESS2016-07-04
ClickatellAustriaA1 MobilKomEuropeWARNING2016-04-14
ClickatellAustriaA1 MobilKomEuropeWARNING2016-07-04
ClickatellAustriaH3GEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-29
ClickatellAustriaH3GEuropeSUCCESS2016-04-29
ClickatellAustriaH3GEuropeWARNING2016-04-29
ClickatellAustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-06
ClickatellAustriaT-Mobile/TeleringEuropeSUCCESS2016-04-14
ClickatellAustriaT-Mobile/TeleringEuropeSUCCESS2016-07-06
ClickatellAustriaT-Mobile/TeleringEuropeWARNING2016-04-14
ClickatellAustriaT-Mobile/TeleringEuropeWARNING2016-07-06
CMSMSProvider"}Austria3/Orange/One ConnectEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-19
CMSMSProvider"}Austria3/Orange/One ConnectEuropeSUCCESS2016-04-19
CMSMSProvider"}AustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-04
CMSMSProvider"}AustriaA1 MobilKomEuropeSUCCESS2016-04-14
CMSMSProvider"}AustriaH3GEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-29
CMSMSProvider"}AustriaH3GEuropeSUCCESS2016-04-29
CMSMSProvider"}AustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-06
CMSMSProvider"}AustriaT-Mobile/TeleringEuropeSUCCESS2016-04-14
CMSMSProvider"}AustriaT-Mobile/TeleringEuropeSUCCESS2016-07-06
MyCoolSMSProvider"}Austria3/Orange/One ConnectEuropeWARNING2016-04-19
MyCoolSMSProvider"}AustriaA1 MobilKomEuropeWARNING2016-07-04
MyCoolSMSProvider"}AustriaH3GEuropeWARNING2016-04-29
MyCoolSMSProvider"}AustriaT-Mobile/TeleringEuropeWARNING2016-07-06
NexmoAustria3/Orange/One ConnectEuropeSUCCESS2016-04-19
NexmoAustria3/Orange/One ConnectEuropeWARNING2016-04-19
NexmoAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-04
NexmoAustriaA1 MobilKomEuropeSUCCESS2016-04-14
NexmoAustriaA1 MobilKomEuropeWARNING2016-04-14
NexmoAustriaA1 MobilKomEuropeWARNING2016-07-04
NexmoAustriaH3GEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-29
NexmoAustriaH3GEuropeSUCCESS2016-04-29
NexmoAustriaH3GEuropeWARNING2016-04-29
NexmoAustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-14
NexmoAustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-07-06
NexmoAustriaT-Mobile/TeleringEuropeSUCCESS2016-04-14
NexmoAustriaT-Mobile/TeleringEuropeSUCCESS2016-07-06
NexmoAustriaT-Mobile/TeleringEuropeWARNING2016-04-14
NexmoAustriaT-Mobile/TeleringEuropeWARNING2016-07-06
SMSAPIProvider"}Austria3/Orange/One ConnectEuropeSUCCESS2016-04-19
SMSAPIProvider"}Austria3/Orange/One ConnectEuropeWARNING2016-04-19
SMSAPIProvider"}AustriaA1 MobilKomEuropeSUCCESS2016-04-14
SMSAPIProvider"}AustriaA1 MobilKomEuropeWARNING2016-04-14
SMSAPIProvider"}AustriaH3GEuropeSUCCESS2016-04-29
SMSAPIProvider"}AustriaH3GEuropeWARNING2016-04-29
SMSAPIProvider"}AustriaT-Mobile/TeleringEuropeSUCCESS2016-04-14
SMSAPIProvider"}AustriaT-Mobile/TeleringEuropeWARNING2016-04-14
ViaNettAustriaA1 MobilKomEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-14
ViaNettAustriaT-Mobile/TeleringEuropeFAILURE_MESSAGE_NOT_RECEIVED2016-04-14
373 Views
0 Likes
sunny_talwar
MVP
MVP
‎2016-09-19 08:08 PM

In response to

In terms of chart, what numbers do you expect to see? I guess I am still not 100% sure how the output needs to look like.

Best,

Sunny

373 Views
0 Likes