5 Replies Latest reply on Jun 30, 2016 4:07 AM by Marie-claire Fournier

# Tricky Top 10 - involving table / LOD calculation & aggregation of result

Hello,

This is my first post, I would usually find solutions to my problems by browsing the forum, but this time I'm stuck.

I'm attaching a simplified version of the twbx to help with the explanation.

Data :

- supplier / date / amount

- flags (in the attachement I've only put 1 flag : with/without PO)

Filters in Context (architecture can not be modified) :

- date

- flags

User interaction :

parameter : frequency

The user is able to display the dashboard with calculation by month / by quarter / by year.

Calculated field : Date Group which is a simple datetrunc based on the parameter

Objectives :

- Identify the top 10 suppliers for each Date Group (after context filters are applied)

- Return pie with % of Spend with the top 10 suppliers against Spend in context.

What I managed so far :

- I've identified the top 10 suppliers for each date group using table calculation Rank_Unique( {fixed [supplier],[date group] , sum([amount])}) <11 calculated across supplier

Struggling with :

- Aggregation of the Spend for the top 10 Suppliers.

I don't seem to be able calculate the sum of amount of the top 10 suppliers without having to place supplier and date group in the details.

The pie is split at supplier and date group where I would only like to split it in or out of the top 10 suppliers.

- I've tried LOD calculation but they don't work with table calculation

something like : {Fixed [Flag top 10 suppliers] : sum([Amount])}

but table calculation can't be used in LOD formula

Has anyone experienced this problem ? Any suggestion ? Changing the approach and not use table calculation ?

Thanks for your help.

Marie-Claire

• ###### 1. Re: Tricky Top 10 - involving table / LOD calculation & aggregation of result

Hi Marie-claire,

Something like this?

Please find the attached.

Yours,

Yuri

1 of 1 people found this helpful
• ###### 2. Re: Tricky Top 10 - involving table / LOD calculation & aggregation of result

Hello Yuriy,

Thanks very much for the quick answer. Unfortunately creating a set doesn't work for this.

It groups the top 10 vendors at vendor level.

I need a group of top 10 vendors at vendor and date group level.

The amount for the top 10 vendors for each date group should sum to : 80,478,779

In the pie you created it sums to : 76,237,461

.

But this is exactly the graph type I'm looking to create.

any idea ?

Thanks

• ###### 3. Re: Tricky Top 10 - involving table / LOD calculation & aggregation of result

I think the correct value is

 R\$   41.123.938,00

Are you sure about 80,478,779?

• ###### 4. Re: Tricky Top 10 - involving table / LOD calculation & aggregation of result

Hi Marie-claire,

Sorry for being quick and dirty with my answer.

Setting Top 10 & Others for each particular Date Group

requires RANK() and Table Calculations after that.

Please find the attached w/mods.

Yours,

Yuri

2 of 2 people found this helpful
• ###### 5. Re: Tricky Top 10 - involving table / LOD calculation & aggregation of result

Hello,

Thanks very much for the answer. Nice trick to split the sum in 2 fields.

Again, thanks !