topic Rank function and set analysis in QlikView

Rank function and set analysis

Thu, 06 Oct 2011 13:44:42 GMT

Hi all,

I have the following problem (which is causing headaches):

I make a selection to compare a month in 2 years. So I select 2 years and 1 month. With one of the expressions, I want the rank based on turnover for my customers (= dimension) ytd,, meaning al months <= the selected month.

The problem is that if a customer does not have turnover in the selected month, my function does not calculate the rank for that customer.

The expression for the turnover untill the selected month for the latest selected year is the following, and does return the correct value:

sum({$<Year={$(=max(Year))},Month={"<= $(=num(Month))"}>} TurnOver)

The expression for getting the rank is the follwing, but does not return the rank for the customers who have no turnover in the selected month (for the other customers this works, but the rank is not correct because of the missing customers)

rank(total

aggr( sum({$<Year={$(=max(Year))},Month={"<= $(=num(Month))"}>} TurnOver),CustomerID)

,0,1)

How can make that second expression to take the customers with no turnover in the selected month into account?

Thx in advance,

Yves

Rank function and set analysis

Thu, 06 Oct 2011 16:28:49 GMT

Yves,

What value is stored in the TurnOver field when a customer has no turnover for that month? Also what is currently being displayed and how do the customers with no turnover need to be handled to correct the problem?

Rank function and set analysis

Mon, 24 Oct 2011 13:52:30 GMT

Hi,

when a customer has no turnover for a month, there will be no records for that month.

Customers need to be ranked by there turnover base upon all of the months, not only the selected month, customers with no turnover for all the months don't need to be displayed.

Yves.

Rank function and set analysis

Tue, 25 Oct 2011 08:11:38 GMT

For enyone interested: I solved this my remaking the table so that users need to select all the months instead of just the last one.

Greetings,

Yves

Rank function and set analysis

rlp — Tue, 31 Jan 2012 09:43:22 GMT

You can remplace $(=num(Month)) by $(#Month) .