Rank over time

[sifatnabil]

Hi all,

I want to have a line chart where multiple clients can be selected, and the clients' rank of sum(revenue) is plotted over month. Right now my dimension is Month and Client, and expression is rank(sum(revenue)).

But if I select a client, the rank is 1 on each month. How can I change the expression to correctly express the client's revenue rank in the month over time?

[sunny_talwar · Accepted solution]

Can you try this:

Rank(Sum({<Client>} revenue)) * Avg(1)

or

Rank(Sum({1} revenue)) * Avg(1)

[sifatnabil]

Hi stalwar1‌, Rank(Sum({<Client>} revenue)) * Avg(1) correctly states the rank of the client I selected, but it shows every other client as '-'. Is there a way to remove the other clients (not in selection) from the chart?

[Anonymous]

RANK(ONLY({1}sum(revenue)))

[sunny_talwar]

Does this make any difference:

If(Sum(revenue) > 0, Rank(Sum({<Client>} revenue)))

[sifatnabil]

No difference.

[sifatnabil]

Hi, this doesn't work. It clears the table/chart completely.

[sunny_talwar]

Would you be able to share a sample to look at this?

[sifatnabil]

Actually Rank(Sum({<Client>} revenue)) * Avg(1) this works once I convert the straight table to a line chart! I just had to remove the legend because it showed all clients. Also, since higher ranks have lower values (e.g. best rank = 1), is there a way to invert the axis such that a 1 is highest on the chart? Then the line chart will be more intuitive. Thanks.

[sunny_talwar]

Check this thread out:

line chart order