Top 5 Ranking - Multiple Conditions

[pnn44794]

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.

[arvind_patil]

Hi Perry,

Give Sample data it will help us to reach destination.

Thanks,

Arvind Patil

[effinty2112]

Hi Perry,

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

cheers

Andrew

[zhadrakas · Accepted solution]

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.

[pnn44794]

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.

[pnn44794]

Hello Andrew:

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

[pnn44794]

Hello Arvind (again):

Sorry I misspoke above.

[pnn44794]

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.

[pnn44794]

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.