Solved: Total of Top n ... - Qlik Community

Report Inappropriate Content · ‎2013-09-09

Hi

I have searched this forum but can not adapt any of the solutions and yield a correct result

I have created a pivot table to show the top n suppliers.

The Expression:

if(aggr(rank(sum({1<[Receipt Year] = {$(vCurrentYear)}> *<[Receipt Month #] = {'<=$(vLastMonth)'}>}[# Currency line value])), [Master supplier no.]) <= 10, MstrSupplierName, null())

The Dimension:

This is held in a variable called vCurrentYear_Spend

sum({1<[Receipt Year] = {$(vCurrentYear)}> *<[Receipt Month #] = {'<=$(vLastMonth)'}>}[# Currency line value])

I can calculate the % of Total for ALL suppliers (this is working correctly).
=$(vCurrentYear_Spend)/sum({$<[Receipt Year] = {$(vCurrentYear)}>} total [# Currency line value])

What I am finding difficult is how to calculate the % of Top 10 because I don't know how to write the formula to sum() the top 10.

I hope this makes sense...

Thanks

Oli

orital81 · ‎2013-09-11

Hi Oli

There is a way to perform this task with pivot table, using a small script addition.

Here is the action plan:

1. Create a table that will hold to sum of top 10 suppliers for every month.

it will have year and total top 10 value.

2. in the chart expression you will use this value as following:

sum({$<[Receipt Year] = {$(vCurrentYear)} value) / sum({$<[Receipt Year] = {$(vCurrentYear)} total top 10 value)

In order to create the new table use something like this:

Sum(If(aggr(rank(sum([Currency line value])), MstrSupplierName) <= 10, [Currency line value], 0)) as [total top 10 value],

Year As YearMonth

Resident YourTable

group by Year

Im sure there are other good ways to do it, such as using a top 10 flag or you can calculate the top 10 in a variable

but this approach will work.

Let me know how it goes for you

Good Luck

View solution in original post

orital81 · ‎2013-09-09

If you already have the Top N Suppliers Value

You can add another expression with the value of

all suppliers for the specific year AGGR(Sum(Value),[Receipt Year])

Then all you need to do is calculate the Ratio:

TopN Value / Total Value.

Is this what you meant?

Report Inappropriate Content · ‎2013-09-10

Hi Ori

Thank you for your reply. As yet I have not managed to get this to work. I think this is my limited knowledge holding me back. I am trying to simply it at the moment by removing the set analysis from the formulas...

Oli

orital81 · ‎2013-09-10

Hi Oli

You are welcome to attach your qvw if you wan't some more help in this issue.

Ori

Report Inappropriate Content · ‎2013-09-10

Hi Ori

I have attached a very simplified example file. I hope none of the complexity I have left out will affect your answer.

Once again, thank you for your assistance.

orital81 · ‎2013-09-10

Hi Oli

There is a simple solution,

If you are using straight table you can use the Relative checkbox

which will show you the percentage.

See my attachment, I have created a new table on the right

Report Inappropriate Content · ‎2013-09-10

Wow, you're fast!!

Do you know of a way to get this If I am having to use a pivot table instead of the straight version?

I am asking because this is currently the case and I am trying to learn as much as possible from my peers for future projects.

Many thanks

Oli

michael_maeuser · ‎2013-09-10

see attached

orital81 · ‎2013-09-10

There is a way to do it with pivot table, but it's a bit more complicated.

Hopefully, I will get to it later on...

Good luck

Report Inappropriate Content · ‎2013-09-11

Hi Michael

Am I missing something? I can not see what you are showing me in the attachment.

If there is some confusion, I am trying to figure out the formula that would result in the value 1,374,942 (the total of the top 10 suppliers).

Oli

Total of Top n ...

Related Topics