topic Rank over time in QlikView

Rank over time

sifatnabil — Wed, 05 Oct 2016 18:20:52 GMT

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?

Re: Rank over time

sunny_talwar — Wed, 05 Oct 2016 18:23:01 GMT

Can you try this:

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

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

Re: Rank over time

sifatnabil — Wed, 05 Oct 2016 18:31:27 GMT

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?

Re: Rank over time

Anonymous — Wed, 05 Oct 2016 18:31:51 GMT

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

Re: Rank over time

sunny_talwar — Wed, 05 Oct 2016 18:33:24 GMT

Does this make any difference:

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

Re: Rank over time

sifatnabil — Wed, 05 Oct 2016 18:35:25 GMT

No difference.

Re: Rank over time

sifatnabil — Wed, 05 Oct 2016 18:35:59 GMT

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

Re: Rank over time

sunny_talwar — Wed, 05 Oct 2016 18:37:00 GMT

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

Re: Rank over time

sifatnabil — Wed, 05 Oct 2016 18:51:21 GMT

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.

Re: Rank over time

sunny_talwar — Wed, 05 Oct 2016 18:54:31 GMT

Check this thread out:

line chart order

Re: Rank over time

sifatnabil — Wed, 05 Oct 2016 18:56:03 GMT

Cheers! Also, what does adding avg(1) actually do to the rank?

Re: Rank over time

sunny_talwar — Wed, 05 Oct 2016 18:58:19 GMT

Avg(1) Doesn't do anything to the Rank. It just remove those rows from viewing where the client has not been selected. This is needed because Rank(Sum({<Client>} revenue)) will disregard selection in client field and will show all the clients and you somehow need to delete them. That's where Avg(1) kicks in.

Re: Rank over time

sifatnabil — Wed, 05 Oct 2016 18:59:24 GMT

How did you know to use avg(1)? It seems like a completely unrelated function!

Re: Rank over time

sunny_talwar — Wed, 05 Oct 2016 19:05:23 GMT

Whenever you don't want your expression value to change, but still want to show selected values, I try to use Avg(1). I guess it might not be straight forward, but if you use this often, this should become fairly intuitive.