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

Announcements
Join us to spark ideas for how to put the latest capabilities into action. Register here!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

3 Replies
sunny_talwar

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

Not applicable
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
sunny_talwar

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