14 Replies Latest reply on Sep 21, 2018 9:19 AM by bhargavi chittibomma

# Create dynamic set to show top 10 and rest as others

Hi,

I am looking for some dynamic set or dynamic solution that would show top 10 and rest as others

I have tried by creating set it is working fine with out any filter condition's on view.. When filter condition's are applied it is not showing the correct result..

Tried to do by creating calculated filed with the index this is working.. but this using table calculation and I cannot use this in other field

IF INDEX()<=10 then ATTR([C Partner Name]) else ('Other') END

case [Select Dimension]

when ('Partner') then ([Partner w Other-For Lead])

when ('Tactic') then ([Sub Tactic w Other-For Lead])

when ('Program') then ([Program w Other-For Lead])

when('CType') then ([Sfdctype])

END

Let me know how can i solve this

Thanks

Bhargavi

• ###### 1. Re: Create dynamic set to show top 10 and rest as others

try with IF and ELSEIF  statement.

Thanks

• ###### 2. Re: Create dynamic set to show top 10 and rest as others

Can you provide me an example

Thanks

Bhargavi

• ###### 3. Re: Create dynamic set to show top 10 and rest as others

Hi Bhargavi,

I don't know if this is what you're looking for, thought I'd just share.

Here's something I created and that works for me, in my example I have multiple types of pizzas and I'm ranking each pizza type by it's contribution to total sales. I call it as "% of Total" in my calc. The calc basically groups all items that contribute less than 1% of total sales or is just NULL. See if this works for you too,

IF ({ FIXED [Item Desc]:[% of Total]} < .01 OR ISNULL({ FIXED [Item Desc]:[% of Total]})) THEN "Others" ELSE [Item Desc] END

Thanks,

Shiva.

• ###### 4. Re: Create dynamic set to show top 10 and rest as others

Thanks but I also want to use that field in other calculated field

Thanks

Bhargavi

• ###### 5. Re: Create dynamic set to show top 10 and rest as others

The field which I wanted to use is dynamic dimension field..

Thanks

Bhargavi

• ###### 6. Re: Create dynamic set to show top 10 and rest as others

Can you sample some data and attach a packaged workbook to work with?

• ###### 7. Re: Create dynamic set to show top 10 and rest as others

I am afraid that I canot provide a sample data.

If we can make index() as string I think my problm can be resolved..

IF INDEX()<=10 then ATTR([C Partner Name]) else ('Other') END

the result of this is .. in dimension

But this in measures area.. And unfortunately I cannot move this on to dimension section. If i can move this in to dimension my problem is solved...

case [Select Dimension]

when ('Partner') then ([Partner w Other-For Lead])

when ('Tactic') then ([Sub Tactic w Other-For Lead])

when ('Program') then ([Program w Other-For Lead])

when('CType') then ([Sfdctype])

END

Select Dimension is parameter...

Partner,Response Channel',Tactic,Program,CType are the list created for that parameter..

Sfdctype

of this "Partner w Other-For Lead" is the dimension which is causing error...because this is created by using index calculation..

Any help on this is greatly appreciated!!!

• ###### 8. Re: Create dynamic set to show top 10 and rest as others

Hi Bhargavi,

Sorry! I don't currently have a solution. Replying to bump this up, so someone else can pick this.

Thanks,

Shiva.

• ###### 9. Re: Create dynamic set to show top 10 and rest as others

ok thanks...

can you think dynamic set would help... ?

The set which I have created is working fine when no filters applied to the view..but when I have some filters applied to the view

it messed up..

Solution on this would help tooo

• ###### 10. Re: Create dynamic set to show top 10 and rest as others

Bhargavi, the problem with your calc is the INDEX() which is forcing it to be a Measure. If you can figure out a way using a LOD instead of a table calc to rank, the field would be placed in dimensions as you wanted. With that being said, I'm not sure why filters are causing issues with the view without actually looking at it. Have you tried context filters? All this is just trial and error with what you've provided.

• ###### 11. Re: Create dynamic set to show top 10 and rest as others

yes working with context filters is what I am doing now.. looks like I should be good.. shall update it when I am able to achieve the result..

I am not sure how to convert index() to LOD I am still looking for this option..

Thanks for the help

..

• ###### 12. Re: Create dynamic set to show top 10 and rest as others

Adding context filter make set to act dynamic.. My problem resolved..

• ###### 13. Re: Create dynamic set to show top 10 and rest as others

Great! If every thing worked, you can close thread.

• ###### 14. Re: Create dynamic set to show top 10 and rest as others

I have marked as assume answered!!

But I still wanted to know alternative to index() using LOD