topic Re: Display Top/Bottom n based on multiple criteria in App Development

Display Top/Bottom n based on multiple criteria

njimack — Thu, 20 Jan 2022 13:32:33 GMT

Hi everyone

Long-time lurker, occasional poster!

I have client metrics going back several years. The relevant fields in my data are:

Client name
Product name
Year
Revenue

I'm trying to create a pivot table with the above, but only for those clients in the Top n for revenue last year.

I've managed to produce charts with just the current year data using this expression:

=if(aggr(rank(if(Year=max(Year),sum(Revenue)))<=var_slider_Top,Client),Client) (where var_slider_Top) is a variable to determine the no. of clients to display)

However, trying to create a pivot that has multiple years data but restricting the client names based on current year data is beyond me 😞

Re: Display Top/Bottom n based on multiple criteria

vikasmahajan — Thu, 20 Jan 2022 14:04:08 GMT

You need to use extension for implementing the same below link can help you to develop :

https://community.qlik.com/t5/New-to-Qlik-Sense/Top-N-Filter-Pane/td-p/1218141

Regards

Vikas

Re: Display Top/Bottom n based on multiple criteria

njimack — Thu, 20 Jan 2022 14:08:45 GMT

Thanks, but creating the variable / slider isn't the issue - I can't figure out the correct expression to display multi-year data for the Top n clients based only on current year.

Re: Display Top/Bottom n based on multiple criteria

vikasmahajan — Thu, 20 Jan 2022 16:45:03 GMT

Please post your expression and sample data to resolve your issue

Vikas

Re: Display Top/Bottom n based on multiple criteria

njimack — Thu, 20 Jan 2022 17:13:13 GMT

Sample data...

Client	Product	Year	Revenue
Client 1	Product 1	2020	100
Client 1	Product 1	2021	150
Client 1	Product 2	2020	50
Client 1	Product 2	2021	200
Client 2	Product 1	2020	20
Client 2	Product 1	2021	40
Client 2	Product 2	2020	60
Client 2	Product 2	2021	80

My task is to create a pivot table containing data for 2020 and 2021, but only for the Top n clients based on their 2020 revenue

I've tried various expressions using a combination of Aggr, Rank and If, but my pivot table isn't displaying any data.

Re: Display Top/Bottom n based on multiple criteria

vikasmahajan — Fri, 21 Jan 2022 08:12:41 GMT

Hi,

I have created Top N Analysis pfa attached file.

Keep only 3rd measure in your report as you required last year top sale.

Hope this solve your issue.

Vikas

Re: Display Top/Bottom n based on multiple criteria

njimack — Fri, 21 Jan 2022 10:13:03 GMT

Thank you! This has pointed me in the right direction but it's still not working as expected (this may be because of the way I explained it).

The two pictures below show a sample of my raw data and the output in my pivot table (customer names masked out). I tweaked what you sent me and the PT is almost there but for some reason is only displaying data for Year 2018.

This is my Mastergroup expression in the PT (to display those Mastergroups who had the lowest EP in 2021):

=IF(Aggr(Rank(-SUM(EP),4),Year=max(Year),Mastergroup)<= $(var_slider_Top),Mastergroup)

Raw data (with customer name masked)Pivot table - only displaying 2018 for some reason