topic Re: Pivot with Top 10 customers AND... in QlikView

Pivot with Top 10 customers AND...

Thu, 05 Jul 2012 22:27:01 GMT

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

Can anyone please help?

Thanks!

NorTor

Norway

Re: Pivot with Top 10 customers AND...

Fri, 06 Jul 2012 06:00:05 GMT

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...

Fri, 06 Jul 2012 13:25:36 GMT

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

sales is the name of the sales field and
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,

Re: Pivot with Top 10 customers AND...

Fri, 06 Jul 2012 18:29:05 GMT

Dear Vicky,

thank you so much for your reply and example file; this is most helpful!

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...

Fri, 06 Jul 2012 18:30:31 GMT

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...

Wed, 15 Aug 2012 08:56:45 GMT

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...

Thu, 16 Aug 2012 08:17:09 GMT

Hi NorTor,

Is it possible for you to post an example?

Regards

Rahul

Re: Pivot with Top 10 customers AND...

Thu, 16 Aug 2012 08:18:44 GMT

Dear Lakhina,

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

Tor Olav

Re: Pivot with Top 10 customers AND...

Thu, 16 Aug 2012 08:20:32 GMT

both would be awsome

Re: Pivot with Top 10 customers AND...

Thu, 16 Aug 2012 10:10:17 GMT

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...

Fri, 17 Aug 2012 07:27:32 GMT

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...

Fri, 17 Aug 2012 08:00:04 GMT

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...

Fri, 17 Aug 2012 11:20:45 GMT

Hi Tor,

Please find attached a solution for your problem.

Best of luck! and have a great weekend

Regards

Rahul Lakhina

Re: Pivot with Top 10 customers AND...

Fri, 17 Aug 2012 11:29:27 GMT

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...

Fri, 17 Aug 2012 12:24:10 GMT

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

Re: Pivot with Top 10 customers AND...

Fri, 17 Aug 2012 12:58:01 GMT

You truly are incredibly nice!

I will test later today and report back.

Thank you Lakhina and enjoy the weekend!

Cheers

Tor Olav

lakhina007 <qliktech@sgaur.hosted.jivesoftware.com> skrev:

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Pivot with Top 10 customers AND...

created by lakhina007<http://community.qlik.com/people/lakhina007> in New to QlikView - View the full discussion<http://community.qlik.com/message/249445#249445>

Re: Pivot with Top 10 customers AND...

Fri, 17 Aug 2012 19:47:18 GMT

Dear Lakhina!

As expected from you: It worked perfectly! ...what a perfect start on the weekend!

Thank you thank you!

Hope you are enjoying the friday night!

Cheers from your #1 fan,

Tor Olav aka NorTor

Re: Pivot with Top 10 customers AND...

Wed, 10 Oct 2012 05:13:49 GMT

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...

Wed, 10 Oct 2012 05:15:23 GMT

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...

Wed, 10 Oct 2012 05:15:32 GMT

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 ?