Rank function used in Line chart for coloring TOP 10 values in last 7 days time (for given Date)

[MATC]

Hello,

I've created the Top 10 functionality using Rank function in table. Similarly I've created line chart using similar function.
I've set coloring to table to reflect 1-10 position and would like to set identical colors throughout line chart.

Line chart represents last 7 days for the top 10 positions from today (Value that is in Top 10 today might not be in top 10 yesterday) And Top's interchange between those days as well. 
Issue is that I can't freeze the top 1 -10 values from today in the whole last 7 days 

Function that I'm using is for coloring :

In 2023-07-08 TOP 1 value switches color with TOP 2 value - (top 1 and 2 for 2023-07-10) as the TOP 2 is in 1st position for 2023-07-08 but i need to make the color consistent with the 2023-07-10 data

if(Rank(count({<Date = {"$(=date(Max(Date)))"},Date_>} VALUE1))=1,RGB(106, 94, 166) ...
- I need to "Freeze" the date as I think that in line chart every dimension value is itself the maxdate that is available. Is there a possibility for that? 

I've also achieved that only the today's date is colored and the rest is gray.  

if(rank(count({<Date = {"$(=date(max(TOTAL Date)))"}>} VALUE))=1,RGB(106, 94, 166)

[Oleg_Troyansky · Accepted solution]

Hi,

I'd suggest two improvements here. 

1. You should add the TOTAL qualifier to your count function - that will allow you to disregard the Date dimension and always get the count for the current date - something like this (assuming that the second dimension is called Product):

if(Rank(count({<Date = {"$(=date(Max(Date)))"}>}  TOTAL <Product>  VALUE1))=1,RGB(106, 94, 166) ...

2. Since the date(Max(Date)) represents today's date, or the highest selected date, it could be calculated outside of the chart, eliminating any guesswork. For example, you could add a variable vMaxDate and calculate it ahead of time:

The definition of vMaxDate should read "(=date(Max(Date))", and then the Set Analysis would look like this:

if(Rank(count({<Date = {"$(vMaxDate )"}>}  TOTAL <Product>  VALUE1))=1,RGB(106, 94, 166) ...

Cheers,

[MATC]

Thanks for replying,

1st solution is not working - It grays out the line - I've played around with TOTAL qualifiers already. Adding the Total qualifier to Max aggregation in date grays out last 6 days and only colors the "today's" date 

2nd solution won't work for me as the end user can select different dates in time so for example last month's date will be represented as "today's" - max(date). 

[Oleg_Troyansky]

I don't think you understood my suggestions at all:

- you played with the TOTAL in the max() function, while you need to have TOTAL in the Count()

- A variable with the formula that begins with an equals sign gets recalculated with each user selection, so it should fit your needs perfectly.

[MATC]

Thank you for reply,

This is my outcome when I use this function:
if(Rank(count({<Date = {"$(vMaxDate)"}>} Total<PRODUCT>))=1,RGB(106, 94, 166) ...

without total in count i get this
if(Rank(count({<Date = {"$(vMaxxDate)"}>} PRODUCT))=1,RGB(106, 94, 166) ...

[Oleg_Troyansky]

Hi,

In your first formula, you lost the field  that you were counting (used to be VALUE1) - you have Set Analysis and the TOTAL <PRODUCT> but no field to count. You should be getting a syntax error on it.

In your second formula, since it doesn't have a TOTAL qualifier, it can only be true for the current date, hence the coloring of the current date only.

I believe that the first formula, after you fix the syntax error in it, should give you the desired result.

[MATC]

Thank you Oleg, indeed i forgot to add the correct dimension after the total quantifier. It is working now thanks 🙂