Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

cafcptg2011
Contributor

Top N list with 2 dimensions

Hi,

I have a Problem with Pivot Tables to get a Top N List.

Problem description:

I have 2 dimensions, Store and Customers, and i want to get for both dimension the top N list.

I have for the first dimension the following line:

=if(aggr(rank(SUM(Quantity),4,0),Store )<=5,Store ) [works great]

and in the other dimension i have:

=if(aggr(rank(SUM(Quantity),4,0),Customers)<=5,Customers) [doen't work, its shows only a top 2 or top 3 per store, bizarre?]

in expression formula , i have =Sum(Quantity)


thanks in advance

cafc

1 Solution

Accepted Solutions
MVP
MVP

Top N list with 2 dimensions

If I understood what you want, you merely need to aggregate the Customer ranking by Customer AND Store instead of just by Customer. See attached example. This example also shows the kind of thing that people can post to the forums when asking questions, and makes it much clearer what the question is, and also allows us to easily make sure our answer is correct.

View solution in original post

9 Replies
Not applicable

Top N list with 2 dimensions

have u tried going to 'presentation' tab and under 'dimension limitations', check the Max visible number and set it 5 ...

cafcptg2011
Contributor

Top N list with 2 dimensions

yes, but I working with pivot table. And pivot table we can't directly do that. so i have those calculate dimensions, do you understant want i mean?

thanks

MVP
MVP

Top N list with 2 dimensions

Well, the top five customers do not necessarily shop in the top five stores. So in theory, your chart could be completely blank. Getting 2 or 3 per store doesn't sound unreasonable, and could be correct. But maybe I'm misunderstanding. Perhaps show some sample data and intended results, or even post a sample QlikView for us to experiment on?

cafcptg2011
Contributor

Top N list with 2 dimensions

Hi John,

first of all, let me thank you for your answer.

yes, is true that "the top five customers do not necessarily shop in the top five stores" . And maybe is for that reason that doen't show want i was expecting. So, what i want? What i want is to know which are the best 10 customers per Store (on the 10 best stores), in same conditions that i refere above, ie, 2 dimensions (strore and customers) and expression = sum(quantity) ?

Could you help me, please?

Thanks,

Cafc

MVP
MVP

Top N list with 2 dimensions

If I understood what you want, you merely need to aggregate the Customer ranking by Customer AND Store instead of just by Customer. See attached example. This example also shows the kind of thing that people can post to the forums when asking questions, and makes it much clearer what the question is, and also allows us to easily make sure our answer is correct.

View solution in original post

cafcptg2011
Contributor

Top N list with 2 dimensions

PERFET!! it's precisely what i wanted!! thank you very much john for your quick answer and qvw sample.

I have a small question related to this problem. Suppose, in this case to complete this matter, that I want to calculate "weight" of top customers from the top of stores in relation to global sales. What expression could I use to archive % of (top N customers of top N stores/global sales) ?

i atteched the qvw file to explain better what i need.

Thanks again for your help and i'm sorry for my newbie questions.

cafc

MVP
MVP

Top N list with 2 dimensions

If, say, we had a table with dimensions Customer and Store, and expression sum(Sales), then we could get the total from the pivot table with this:

sum(aggr(sum(Sales),Customer,Store))

That's overkill for our specific case, because a simple sum(Sales) gives us the same total, but it's just an example of the syntax for totalling values in a pivot table. In this case, the very first pivot table in the example. Now we're using calculated dimensions in our real pivot table, so I tried literally inserting those calculated dimensions instead:

sum(aggr(sum(Sales)
,aggr(if(rank(sum(Sales))<3,Store),Store)
,aggr(if(rank(sum(Sales))<3,Customer),Store,Customer)))

No luck. It returns zero. Perhaps I just can't use an aggr() in an aggr() this way. But even if it worked, I figured it could be simplified by thinking about it a little more critically, so let's do that.

First, our stores are fixed in a way that our customers are not. We should be able to set them with set analysis somehow. This works for that part:

sum({<Store={"=rank(sum(Sales))<3"}>} Sales)

The customers differ by store, so can't be done in set analysis like that. We'll probably need to aggregate by customer within store:

sum(aggr(sum({<Store={"=rank(sum(Sales))<3"}>} Sales),Store,Customer))

That gives the same answer, just the sales for stores A & B, so so far so good. If I can't do the customer condition with set analysis (since a set is evaluated once for the chart, not once per store), then maybe I can do it with an if():

sum(aggr(if(rank(sum(Sales))<3,sum({<Store={"=rank(sum(Sales))<3"}>} Sales)),Store,Customer))

Took a few tries to get it right, but yes, that works. For consistency with our selections, I should probably use *= in the set analysis. And of course we need to divide by sum(Sales) to get our percentage:

sum(aggr(if(rank(sum(Sales))<3,sum({<Store*={"=rank(sum(Sales))<3"}>} Sales)),Store,Customer))/sum(Sales)

And here's an unexpected benefit of this approach - calculated dimensions tend to be slower than expressions in my experience, and this expression should give us another way to solve the ORIGINAL problem of the pivot table, but without using calculated dimensions. The reason is that its structure is an aggr(...,Store,Customer). That expression mimics a table with those two dimensions, which is what we wanted in the first place. Should be able to just turn that expression into a pivot table:

Dimension 1 = Store
Dimension 2 = Customer
Expression = if(rank(sum(Sales))<3,sum({<Store={"=rank(sum(Sales))<3"}>} Sales))

Unfortunately, no, it doesn't seem quite right for a pivot table. It requires our Store dimension to come before our Customer dimension, or it gives the wrong results. That could be OK if we turned off pivoting. The other problem, and I'm probably just missing something, is that it's returning all rows, even when calculating null or 0, even though I suppress zero values.

cafcptg2011
Contributor

Top N list with 2 dimensions

Its works perfect!! Thank you john again, for the excellent explanation / solution to the exposed problem.

Regards,

Cafc

gauthamchilled
Contributor

Re: Top N list with 2 dimensions

What if i want to 'Show Others' apart from top n at the bottom of the row?