Rank function and set analysis

[#1]

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

[#2]

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?

[#3]

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.

[#4]

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

[rlp]

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