topic Re: Total of Top n ... in QlikView

Total of Top n ...

Mon, 09 Sep 2013 11:58:41 GMT

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

Re: Total of Top n ...

orital81 — Mon, 09 Sep 2013 12:14:37 GMT

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?

Re: Total of Top n ...

Tue, 10 Sep 2013 07:40:48 GMT

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

Re: Total of Top n ...

orital81 — Tue, 10 Sep 2013 08:08:22 GMT

Hi Oli

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

Ori

Re: Re: Total of Top n ...

Tue, 10 Sep 2013 09:09:59 GMT

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.

Re: Total of Top n ...

orital81 — Tue, 10 Sep 2013 09:23:30 GMT

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

Re: Total of Top n ...

Tue, 10 Sep 2013 09:29:24 GMT

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

Re: Re: Total of Top n ...

michael_maeuser — Tue, 10 Sep 2013 09:34:11 GMT

see attached

Re: Total of Top n ...

orital81 — Tue, 10 Sep 2013 09:56:57 GMT

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

Re: Total of Top n ...

Wed, 11 Sep 2013 14:54:14 GMT

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

Re: Total of Top n ...

Wed, 11 Sep 2013 14:56:03 GMT

Hi Ori

Any luck?

I think this technique will help me quite often, but I have not yet figured it out.

Oli

Re: Total of Top n ...

orital81 — Wed, 11 Sep 2013 18:35:03 GMT

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

Re: Re: Total of Top n ...

michael_maeuser — Thu, 12 Sep 2013 07:05:33 GMT

sorry here it is

Re: Total of Top n ...

Thu, 12 Sep 2013 07:17:50 GMT

Hi,

Please try with the firstsortedvalue