29 Replies Latest reply: Oct 15, 2012 5:12 AM by Rahul Lakhina

# Pivot with Top 10 customers AND...

I would like to show the sales revenue of the top ten customers (ranked by revenue). I have solved this.

Also I would like a subtotal of these, and on the next row, the subtotal of all the other customers (number 11 and up).

At bottom I would like a grand total for all customers, (number 1 and all).

So..

Customer 1

Customer 2

.

.

Customer 10

SUBTOTAL TOP 10

ALL OTHER CUSTOMERS

Grand TOTAL

Thanks!

NorTor

Norway

• ###### Re: Pivot with Top 10 customers AND...

Hi NorTor,

One way is to use the valuelist.

Onother way is you build up a new field in script.

See attached example.

Regards Vicky

• ###### Re: Pivot with Top 10 customers AND...

Dear Vicky,

I will test if I can make it work on my dataset later today.

Have a good weekend

Cheers,

NorTor

• ###### Re: Pivot with Top 10 customers AND...

Hi,

This can be achieved by using calculated dimensions i.e. if you are using QV 10 or above. You would have to create an additional dimension using the following syntax:

Category= aggr(if(rank(sum(sales))<= 10, 'Top 10', 'Others'),name)

You can get the list of top 10 customers using the following syntax:

Top 10 Customers = aggr(if(rank((sales))<=10,name,'Others'),name)

Where

1. sales is the name of the sales field and
2. name is the customer field

Once you have those to you can use partial sum from the presentation tab to accomplish your goal.

Hope this help!

Cheers!!

Regards,

RL

• ###### Re: Pivot with Top 10 customers AND...

RL,

thank you for providing this solution! I will try both this way and Vicky's method later on today / weekend!

...Great community; finding solutions day by day :-)

Thanks and cheers back to you!

NorTor

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina007!

Thank you for for help on this a few weeks back, it worked very well indeed!

I am now struggling with a little expansion on the above: Customers can be intercompany or not. I have separate field identifying this on each sales row. Also the internal customers are spread over the different continents (also a separate field stating which). And I would like the internal customers grouped per continent, while the external should be per customer name like before.So, like this is what I am trying to produce:

Internal customers Europe

Internal customers North America

Internal customers Asia

.

.

SUBTOTAL INTERNAL CUSTOMERS

External Customer 1

External Customer 2

.

.

External Customer 10

SUBTOTAL External customers TOP 10

ALL OTHER EXTERNAL CUSTOMERS

TOTAL EXTERNAL CUSTOMERS

Grand TOTAL

Any chance of helping me on this one pretty please?

Cheers,

NorTor

• ###### Re: Pivot with Top 10 customers AND...

Hi NorTor,

Is it possible for you to post an example?

Regards

Rahul

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

Yes absolutely... example of the data or the finished QV table or both?

BR

Tor Olav

• ###### Re: Pivot with Top 10 customers AND...

both would be awsome

• ###### Re: Pivot with Top 10 customers AND...

See .jpg file for what the table looks like in Excel... and I would like to have in QV :-)

This is an example of the data table. There are roughly 2000 lines in a normal week. Normally 150 external customers and maybe 20 intercompany ones.

As mentioned I would like to show intercompany sales grouped into the continents where they belong, while external customers should be by customer name.

Also notice that the rank has to be dynamic for external customers as intercompany customers (count of these) will vary for different time periods, so rank to get top 10 (or top 20 as shown in image included) will have to be done for external customer sales only.

Any help very much appreciated Lakhina, and thank you so much in advance for replying and looking at this.

Cheers

Tor Olav

• ###### Re: Pivot with Top 10 customers AND...

Hi Tor,

Could you post the Excel so i can import it into QV and work off it?

Regards

Rahul

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

please find the xls file. Notice that this one only has a few customers. Normally there will be like 150-200 external clients and 10-30 intercompany ones on a monthly reporting basis.

Thank you again!

Have a nice weekend very soon!

Tor Olav

• ###### Re: Pivot with Top 10 customers AND...

Hi Tor,

Best of luck! and have a great weekend

Regards

Rahul Lakhina

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina!!

I am most grateful for your effort and so quick in doing so! I will try this solution tonight on the full dataset, and based on your earlier solutions I am confident it will work as I hoped for!

Thanks and respect from me!

Tor Olav aka NorTor

• ###### Re: Pivot with Top 10 customers AND...

Hey NorTor,

Thank you for the kind words!

While going through the file i found it was not working as per my designe and had a bug which i have taken care of in V2 attached.

Hopefully this will work exactly as you had hoped!

Regards

RL

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

The solution is great, it is working for me too.

By any chance, do you have any idea how the show Top 5 Customers and sorted by their Sales ?

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

The solution is great. It is working for me as well.

Btw, is there any chance that we can show the Top 5 Customers sorted by their Sales value ?

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

The solution is great. It is working for me as well.

Btw, is there any chance that we can show the Top 5 Customers sorted by their Sales value ?

• ###### Re: Pivot with Top 10 customers AND...

Dear Tor,

Was not sure of your requirements, therefore i have given you the top five customers irrespective of Cutomer Type. The new Top 5 Customer table in the file attached give you your top 5 customers accross the company and groups all others in 'Other Customers' which are stacked at the bottom.

Hope this is what you were after.

Regards

Rahul

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

I would like to get the result of Top 5 Customers being Sorted by their Sales Value

Just take the example for Customer Type = InterCompany,

Customer Type          Top 5                         Top 5 Customers          Sales

InterCompany            Top 5 Customers        Asia                              10519.91

Europe                         5617.99

AMericas                      3628.35

If Customer Type is External, and under Top 5 Customers, then the Top 5 Customers Column should start from the biggest Sales which are

MJ     18995.19

Metro  7857.02

CALA     3135.01

RYGJA     595.66

SAND     442.42

Thank you.

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

Is it possible to achieve the above ?

Thanks.

• ###### Re: Pivot with Top 10 customers AND...

Like this!

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

Unfortunately not or maybe I didn't get your point from  your qvw file.

Basically, my question is how to sort based on the sum(value) of the calculated dimension.

Lets take Example 1 from your qvw

The Top 5 Customers of External, there are 5 which show in the table as

RYGJA - \$ 595.66

MJ - \$ 18995.19

CALA - \$ 3135.01

METRO - \$ 7857.02

SAND - \$ 442.42

I wish that it can be sorted descending based on Sum(Value) of the calculated dimension Top 5 Customers.

MJ - \$ 18995.19

METRO - \$ 7857.02

CALA - \$ 3135.01

RYGJA - \$ 595.66

SAND - \$ 442.42

Is it possible ?

• ###### Re: Pivot with Top 10 customers AND...

Hi Tor,

I guess this why i have been stumped with your request. As in my copy or the copy that I sent to you the data is already sorted.

This has been achived by using an expression to sort the Top 5 Customers please see QVW for detail.

Regards

Rahul

• ###### Re: Pivot with Top 10 customers AND...

Dear Lakhina,

Sorry.. that's my mistake. It seems it didn't run using Qlikview 8.5 client. So I change to Qlikview 10 and yes, it is showing exactly as per your image.

Unfortunately, I still can't get it correct with my qvw. Sorry, I am quite new in Qlikview.

Could you help me with the data that I have ?

This is the source data (data_table_Jor)

And the other is expected_result_Jor which contains 2 worksheet.

Could you help me on that ? I tried to simulate based on your qvw but the result is always wrong, so I believe I have not understood much on the calculate dimension.

• ###### Re: Pivot with Top 10 customers AND...

Dear Tor,

It cannot be sorted individually by the month in the same table (as we are using month as a dimension) but it can be sorted by overall results i.e. sum of all months together. The same table should reflect the results of individual month on selection.

Hope the example attached works for you.

Regars

R

• ###### Re: Pivot with Top 10 customers AND...

Unfortunately, due to the top 10 Charges can be different each month thus, the sorting must be done individual by month. If using your qvw sample, the top 10 Charge will be the same always or the value will not be sorted out correctly in the month.

Is there any other way by adding other dimension to help maybe ?

Thanks.

• ###### Re: Pivot with Top 10 customers AND...

Hi,

How are you? Hope you had a great weekend!

The only thing that can be done is creating a variable and letting the user decide what Month they want the table to be sorted on.

Please see example! I do not think anything else would work sorry.

Please mannualy enter Months 7 to 9 in the InputBox called VSortByMonth to see how the tables change.

Sorry could not be of much help

Regards

R