topic Re: display rank in text object in QlikView

display rank in text object

hopkinsc — Tue, 11 Sep 2012 09:46:46 GMT

Hi,

I have the following data for week 5..

Table1:

Company Site Sales

Company 1 Site 1 1000

Company 1 Site 2 1800

Company 1 Site 3 1250

Company 1 Site 4 2500

Company 2 Site 1 1700

Company 2 Site 2 2000

Company 2 Site 3 4250

Company 2 Site 4 1500

Company 3 Site 1 4000

Company 3 Site 2 9800

Company 3 Site 3 1450

Company 3 Site 4 2800

Table 2

Company Avg Sales Rank

Company 1 1637 3

Company 2 2362 2

Company 3 4512 1

Each Company has its own tab within the application. We want to display the rank of 'Customer 1' in a text box (Obviously the rank will be based on a week).

e.g.

Week 5 is selected.

The text box will display Customer 1's rank (3)

We also want to display the Site rank too.

So for example,

In week 5, the best site for customer 1 is site 4 which ranks no.5

Can anyone help please?

Re: display rank in text object

hopkinsc — Tue, 11 Sep 2012 11:53:59 GMT

Any ideas on this?

Re: display rank in text object

swuehl — Tue, 11 Sep 2012 11:59:42 GMT

Maybe

=only({<Company={'Company 1'}>} aggr(rank(avg(Sales)),Company))

for the rank across Companies and

=only({<CompanySite={"=rank(sum({<Company={'Company 1'}>}Sales))=1"}>} aggr(rank(sum(Sales)),CompanySite))

for the rank of the max sales sites across all sites (I created an additional field Company&Site as CompanySite )

Regards,

Stefan

Re: display rank in text object

hopkinsc — Tue, 11 Sep 2012 14:35:37 GMT

Thanks Stefan, that worked.

Re: display rank in text object

hopkinsc — Fri, 14 Sep 2012 10:09:13 GMT

Hi Stefan,

I have a problem with the answer you gave me..

I tested it in an example script with made up fields. I have now given it to a client to try and they have come back with a much more complicated query.

They work out average sales by using the following expression

Sum({1<'Channel Label' -= {Web, 'Dir*'},SalesWeek={$(=vSalesWeek)}

>}Valid_AmountSummary)/

Count({1<'Channel Label' -= {Web, 'Dir*'},SalesWeek={$(=vSalesWeek)}

>}store_name)

So now i need to get the result as described in my original question but by using the above expression for Avg Sales.

'Store_Name' is the field name for 'Customer'

This is just for the first part of the question. They havent given me the field name as yet for the 'Site' field.

Can you help?

Thanks

Re: display rank in text object

swuehl — Fri, 14 Sep 2012 15:56:53 GMT

Maybe I am missing something here, but you should be able to replace the avg(Sales) in the above rank() function with your calculation of average. Have you tried that?