topic Group by Dimension based on Max Sales in QlikView

Group by Dimension based on Max Sales

Wed, 12 Nov 2014 20:30:36 GMT

I have a scenario where a customer can exist in multiple territories. I want to show a list of customers where only the territory with the most sales are shown, but still have the total sales, independent of territory.

Example of data (other fields exist, such as month):

Customer	Terr	Priority	Sales
Organization 1	1B2	High	100
Organization 2	1B2	Medium	100
Organization 2	1B3	Medium	200
Organization 2	1B4	Medium	300
Organization 3	2B1	Low	100
Organization 3	2B3	Low	200
Organization 4	2B3	High	100
Organization 5	2B4	Medium	100
Organization 6	2B4	Low	100
Organization 7	2B4	High	100
Organization 7	3B1	High	200

This is how I would like to show the data, preferably without changing the load script:

Customer	Terr	Priority	Sales
Organization 1	1B2	High	100
Organization 2	1B4	Medium	600
Organization 3	2B3	Low	300
Organization 4	2B3	High	100
Organization 5	2B4	Medium	100
Organization 6	2B4	Low	100
Organization 7	2B4	High	300

How can I build the Territory dimension to accomplish this?

Thanks for any help that you can provide!

Sam

Re: Group by Dimension based on Max Sales

Oleg_Troyansky — Wed, 12 Nov 2014 23:15:00 GMT

List Territory as an Expression, using the function FirstSortedValue, with the amount of Sales as the sorting parameter.

Re: Group by Dimension based on Max Sales

Thu, 13 Nov 2014 00:48:34 GMT

Thanks for the quick response.

I just tried this and although the formula validates, I cannot get it to return the correct results. Here is my formula:

=FirstSortedValue([Terr], (-aggr(sum( { < [Month]=, [Month Rank] = {1,2,3}, [Brand]= {'BRANDA'} >} [Sales]), [Customer])))

Any idea what I am doing wrong? My understanding is that adding the minus sign before the aggr function will change it to the last sorted value (Largest sales number). I can't see any specific logic to how the Terr is selected.

Re: Group by Dimension based on Max Sales

Thu, 13 Nov 2014 15:51:39 GMT

Hi,

Try this:

Dimension: Customer

Expression 1 (Terr): FirstSortedValue(Terr,-aggr(Sum(Sales),Customer,Terr))

Expression 2 (Priority) : FirstSortedValue(Priority,-aggr(Sum(Sales),Customer,Terr))

Expression 3 (Sales): Sum(Sales)

Regards!

Re: Group by Dimension based on Max Sales

Thu, 13 Nov 2014 18:42:59 GMT

Very helpful Francisco!

Last thing. I have one example where the sales are the same for an account in each territory. It returns null and I am fine with it showing either territory. How can I have it populate with a territory?

Re: Group by Dimension based on Max Sales

Fri, 14 Nov 2014 07:59:11 GMT

Hi ,

I think that using distinct you can solve the problem:

FirstSortedValue(distinct Terr,-aggr(Sum(Sales),Customer,Terr))

The function (FirstSortedValue) has a parameter for this case (n as order), but i've tried apply it unsuccessfully...

Regards

Re: Group by Dimension based on Max Sales

Wed, 19 Nov 2014 19:17:31 GMT

Perfect. Thanks!!!