Jon Foote
/ Categories: Blog

Rank by Value with Customer filtered

I was recently working with a client who was developing a dashboard that could be used to review individual client results. One of the KPI’s they wanted to look at was the clients rank compared to the rest, without showing the values for other clients.
So in essence we needed to create the rank value for each client and show it while the client was selected in the filter list.
I’m using my dummy app to demo the process I went through to solve this and show the ranking.
The first thing to do was create a standard table to give us the correct values.

 

Customers ranked by Amount
In this case the ‘Rank by Amount’ column is calculated using the simple Rank functions;

RANK ( sum ( Amount ) )

However if I was to select a Customer like Spokes, who is shown above ranked 11th, they will now be the only customer and therefore be ranked 1st

 

We wanted to be able to show the rank of the customer in a KPI object while it was selected.

To achieve this I used the expression below. It uses Aggr to aggregate the expression on the customer name, as well as using Set Analysis to ignore the Customer filter.

Aggr ( RANK (   sum( {1< [Customer Name]= >} Amount ) ) , [Customer Name] )

 

Adding this calculation into the same table we can see it shows Spokes as 11th again

 

This value was then added to a KPI object, and as a reference, the total number of customers was included as shown below.

 


For reference, the calculation for total customers was

               Count ( {1} distinct [Customer Name] )

 

Note: Dealing with Null values of Customer.

During testing I noticed I could have a null value for Customer in the calculations. This null value could be removed from the table be deselecting (show Nulls) however it was still calculated for in the filtered KPI value. To remove the Null value in the KPI, so that I am only ranking actual customers, I modified the calculation to;

   Aggr (  RANK ( sum( {1< [Customer Name]= , [Customer Name] -= {NULL} >} Amount ) ) , [Customer Name] )

If you have any questions or comments please feel free to get in touch with me:

 

can be contacted at:

Jon Foote 

Senior Qlik Consultant with Acumen BI

E-Mail: jonf@acumenbi.co.nz

Mobile: 027 423 8132

 


Previous Article Qlik Sense Version 3.0 Available now
Next Article Qlik Public Training - October 2017 in Wellington
Print
848

Please login or register to post comments.

B2B Trading Platform Stats
2016 - present
Total value of all B2B orders
Total number of orders placed
Number of registered B2B users
B2B users logged in last 24h
Learn more about our B2B Trading Platform
Are you bogged down in Excel?    SOLVE IT
You must be in Edit Mode to enter content, and then you can use the Module Action Menu or Module Action Buttons to manage content
Contact Us
  •   Phone:

    +64 (0)9 526 1400

  •   Email:

    sales@acumenbi.co.nz

  •   Physical Address:

    Floor 2, 101 Station Road, Penrose
    Auckland 1061

Find Us