topic Re: Top 5 Ranking - Multiple Conditions in QlikView

Top 5 Ranking - Multiple Conditions

pnn44794 — Wed, 25 Nov 2020 16:16:04 GMT

Hello:

I'm trying to create multiple, either straight tables or pivot tables (pivot tables are the preference) that show the top 5 project_id's by total effective_item_qty for specific "foundation 1" items. If it's possible to create one straight table or one pivot table and then just filter, that would absolutely work.

As always, any and all help is appreciated. Thanks in advance.

Re: Top 5 Ranking - Multiple Conditions

arvind_patil — Mon, 31 Jul 2017 05:49:18 GMT

Hi Perry,

Give Sample data it will help us to reach destination.

Thanks,

Arvind Patil

Re: Top 5 Ranking - Multiple Conditions

effinty2112 — Mon, 31 Jul 2017 07:31:45 GMT

Hi Perry,

Please re-upload you data with foundation1 and foundation 2 fields saved as values to your spreadsheet.

cheers

Andrew

Re: Top 5 Ranking - Multiple Conditions

zhadrakas — Mon, 31 Jul 2017 07:43:28 GMT

you could try this as calculated Dimension:

=If(
Aggr(
Rank(
Aggr(sum(if( project_include_in_forecast =1 AND project_is_active = 1
and estimate_include_in_forecast=1 AND estimate_is_active =1
and effective_item_action='Add' ,effective_item_qty)
)
, project_id)
, 3, 1)
, project_id) <= 5,
project_id)

Dimensions:

- see code above (tick the Option "suppress when value is null")

- oder_date_quarter

Expressions:

sum(effective_item_qty)

this will give you top 5 Projects based on sum(effective_item_qty)

you can still select any quarter period to get the top 5 fot that range.

And you can select any foundation1.

Re: Top 5 Ranking - Multiple Conditions

pnn44794 — Sun, 13 Oct 2019 01:34:33 GMT

Hello Arvind:

Thanks for the reply. I take it you're unable to open the sample data I attached to my initial post?

My apologies for the confusion and delay in replying.

Re: Top 5 Ranking - Multiple Conditions

pnn44794 — Sun, 13 Oct 2019 01:35:35 GMT

Hello Andrew:

Thanks for your reply. My apologies for the delay in responding back. Sorry for the confusion.

Re: Top 5 Ranking - Multiple Conditions

pnn44794 — Sun, 13 Oct 2019 01:36:17 GMT

Hello Arvind (again):

Sorry I misspoke above.

Re: Top 5 Ranking - Multiple Conditions

pnn44794 — Mon, 31 Jul 2017 20:26:59 GMT

Hello Tim:

Sorry for the delay in replying. Your solution definitely works if I only include the fields in the calculated dimension. I get the top 5 rows for, as an example, catalog_item_class_tier2 = VSI DEDICATED. One row per project_id and that's the desired outcome.

When I start adding additional fields though, I get multiple rows per project. I still only get 5 project_id's, but multiple rows for each one. Each project_id can have multiple rows with the same catalog_item_class_tier2 value with different amounts and in some cases the same amount.

I'm guessing that I either need to add the additional fields into the calculated dimension, comma separated, after project_id or I need to do something with the Expression, in order to limit the output to just 5 rows, one per top 5 project_id's???? Thanks in advance.

BTW, I currently have this in a straight table and I'll be trying a Pivot table a little later.

Re: Top 5 Ranking - Multiple Conditions

pnn44794 — Sun, 06 Aug 2017 23:39:46 GMT

Hello Tim:

I am going to mark your answer as the correct answer because it does indeed work. Hopefully, you'll be able to take a look at my follow up questions. I do appreciate your help. Thanks again.