Coloured the line chart

[Anonymous]

Hi,

Is it possible to set something by expression that allows me to view the chart colour based on the rank?

For example I always want that the highest line in the chart has always blue colour, the second yellow, the third black and so on...

My line chart it is a standard Line chart based on 2 dimensions Date and Product Name and one measure Sales.

The dimension Product Name has the following expression:

If(

  Aggr({<Date={">=$(=Num(Today() - 11))"}>} Rank(Sum(Sales)) , [Product Name])<=10 ,

  [Product Name]

)

Any help will be really appreciated.

Thanks,

Giulio

[petter]

Something like this might work well for you ....

Just be aware of adjusting the Rank()-function with the right parameters so you get a unique ranking or you might get two or more lines with the same color. There is a shared ranking that is default I think....

[Anonymous]

Hi Petter,

Thank you very much for your solution, but doesn't work in my graph because I don't have the lines as separated as yours. Other than that my dimension is set in order to display the best 10 product in the last 10 days, but the chart display data start from 2015 (so I could not use the rank function as I have written for my Product dimension.

I have tried with 2 function:

pick(rank(aggr(Sum(Sales),[Product name],Date)),red(),green(),yellow(),black(),White())

and pick(rank(Sum(Sales)),red(),green(),yellow(),black(),White()), but the result is the same (see the image).

I want that the colour will be consistent across the different dimension (Product name).

If I aggregate only per [Product name] the chart will be coloured only in one point per line. I could not reproduce this logic in tables I have tried so many times. The fact is that I want to coloured a dimension by using the ranking expression, but doesn't work.

!

[petter · Accepted solution]

To make it the same color you can just add a TOTAL <[Product Name]> in expression like this:

Pick(Num(Rank(Sum(TOTAL <[Product Name]> Amount))),Blue(),Yellow(),Black(),Red())

[Anonymous]

Many Thanks Petter,

it works!! one more things, due to the fact that my dimension display the best 10 product in the last 10 days, it is possible to add a set analysis restriction at your expression in order to assign the first colour at the best product in the last 10 days and so on?.. I am trying but without any success.

set analysis to add: {<Date={">=$(=Num(Today() - 11))"}>}

Thank you very much

[petter]

‌I would think that it would do exactly that with the last expression I suggested since the expression use the current selection and you are displaying the last ten days.

[Anonymous]

Hi Petter,

with this solution I have some problem because my product doesn't have the same launch date so seems that the colour doesn't remain costant among different period (see attached image). In addition to this with the colour by expression the label that show me the match between colour and product has been disappear.

Do you have a solution for this problem?

[petter]

Maybe start a new question and close this one instead of continuing indefinitely with new challenges too much. It is more beneficial for the community to have rather specific questions that doesnt run too much off topic? What do you think?

[Anonymous]

You are right, but the fact is that the solutions that you have gave me are very good but doesn't fit at 100% for my original question. BTW I will close this sessions and I will open a new one with the original question and this specification.

Thanks

[petter]

This forum is not an exam you know - so pointing people in the right direction definitely qualify as answers in many cases