1 2 Previous Next 19 Replies Latest reply on Jul 26, 2016 6:19 AM by Luciano Vasconcelos

# Top 10 or 2nd top10 Values in Calculated field

HI

Is it possible to create Calculated Field like

sum(Sales) by state top 10.

Sum(sales) by state 2nd top 10

After I have to display

Category,Top10,2nd Top10

Thanks

Govind R

• ###### 1. Re: Top 10 or 2nd top10 Values in Calculated field

Hi Govind,

What is a second top 10? Do you mean 11 - 20? Would you be able to show a mock up? A bit confused how you are attempting to show.

Cheers

Carl Slifer

InterWorks

• ###### 2. Re: Top 10 or 2nd top10 Values in Calculated field

Hi Govind,

I workaround that I think about would be to use an index function as a filter so you give the end user the ability to set up the min/ max rank of your states by sales.

The index function can be use as a filter and will return the row following the sorting you have set up.

Hope this helps,

• ###### 3. Re: Top 10 or 2nd top10 Values in Calculated field

Yes 2 nd top 10 means (From 11 to 20).

• ###### 4. Re: Top 10 or 2nd top10 Values in Calculated field

If Index()<= 10 Then "Top 10"

Elseif Index()<= 20 Then "2nd Top10"

Else "Others"

End

• ###### 5. Re: Top 10 or 2nd top10 Values in Calculated field

My requirement like below.Single measure.If you want you can use sample data store.

My report should be below formate.Can you pls help me.

Category,Top10 sales by state,Top 11 to 20 Sales by state,Remaining sales.

• ###### 6. Re: Top 10 or 2nd top10 Values in Calculated field

Create this calculated field:

If Index()<= 10 Then "Top 10"

Elseif Index()<= 20 Then "2nd Top10"

Else "Others"

End

and drag it into color card

• ###### 7. Re: Top 10 or 2nd top10 Values in Calculated field

This won't work Luciano. Since Top 10, Top 10-20 and remaining need to be calculated by 2 dimension, If use this this kind of index without addressing the how you are computing it then you won't get the answer.

If you have created the output on twbx please share.

-Ashish

• ###### 8. Re: Top 10 or 2nd top10 Values in Calculated field

Hi All,

He wants something like below. Please refer to the screenshot.

For every category there will be customers, so by category I have grouped customers into three groups as you can see the same in the screenshot.

So He wants a single bar for Furniture category which will be Sum of Top 10 customers, stacked with next top 11-20 customers and then the remaining. So bar should be of 100% (% share of grouped customer by Category total)

I have built something like this which is nowhere near the output but it will help to get things started. If you can see First bar for the furniture is the expected output. If see other bars, is having problem with sorting which can be taken care with the index function. This is happening since I have added the customers in the details shelf. I guess Ivan Young can help with this as he has solved the earlier similar problem.

I am trying to fix things since afternoon but couldn't do much with this. I hope this requirement is much clear now.

Thanks and Regards,

Ashish Chaudhari

2 of 2 people found this helpful
• ###### 9. Re: Top 10 or 2nd top10 Values in Calculated field

Hi Stephane,

Customers should not be the part of the view.

-Ashish

• ###### 10. Re: Top 10 or 2nd top10 Values in Calculated field

Hildebrando Souza Jr, tentei fazer mas faltou recurso técnico. Parece aquele do Ferrucio.

Você consegue ajudar?

• ###### 11. Re: Top 10 or 2nd top10 Values in Calculated field

Hi Shinichiro , can you help with this one? everyday I am getting stuck with Top 10 like calcs.

-Ashish

• ###### 12. Re: Top 10 or 2nd top10 Values in Calculated field

I'm a bit busy today, give me several hours.

Shin

• ###### 13. Re: Top 10 or 2nd top10 Values in Calculated field

The data set is different,then you need to replicate all the formula.

I try to explain the background, but if you have trouble, please attach your exact workbook.

To make sort appropriate always, create calculated field.

In this case, Region + Customer Name

[Region + Customer Name]

[Region]+[Customer Name]

[Index]

RANK_UNIQUE(sum([Sales]))

[Filter one value]

if [Index] = 10

or [Index] = 20

or last()=0

then "Show" else "Hide" end

[Sales (Calculated)]

if index() =10 or index() =20 then window_sum(sum([Sales]),-9,0)

elseif index()>20 then window_sum(if index()>20 then (SUM([Sales])) END) END

[Sales (%)]

[Sales (Calculated)]/attr({fixed [Region]:sum([Sales])})

[Top10,20 Color]

if [Index]<=10 then "Top10"

elseif  [Index]<=20 then "2nd Top10"

else "Other"

end

Thanks,

Shin

3 of 3 people found this helpful
• ###### 14. Re: Top 10 or 2nd top10 Values in Calculated field

Hi

Above steps I followed and completed. But till not able to see any char information, Can you please help.

Find the attachment.

I am not able to attaché my tbwx file.

Thanks

1 2 Previous Next