Order Day of the week in a combo graph depending on today

[robinl39529461]

Hi all, 

I created a combo graph to show by day of the week, evolution of request.

The 2 lines represents the last 7 days and last 8-14 days based on rolling dates:

The data source is this one:

I only used the 2 last columns for the visualization.

The date range are rolling dates and the question I received is : "Can we in the visualization adapt the order of the legend depending on the current day?"

If I open it on a monday, the first item will be Sunday, if I open it on a Friday the first item is the Thursday.
Reason of that is to have consistent graph: no having the begin of the line about current week and the end about previous week. It will give wrong impression.

I wanted to force the order the Days of the week based on a metric (that I won't show in the graph) but I don't find any specific metric that can help. 
Any idea or proposition with maybe another visualization?

Thanks

Robin

[Jennifer_Dungan]

So this might sound a little crazy, but you could create a calculated metric to apply a numeric value to Day of the Week... then you can sort by that numerical number....

So I did a test, I only did this from Sunday to Tuesday to prove it works.

Basically this relies on using nested "IF" functions, and static numbers.

Essentially, add an IF function

This has three parts (logical test, value if true, value if false)

In the logical test add:

Day of Week - Sunday

 - metric occurrences (this is just to a generic use to ensure some data - the value won't be used)

In the value if true add:

static number = 1

in the value if false add:

(add a new IF statement and repeat the above for "Monday" and use "2" as the static number, then continue nesting each new day, incrementing the static number each time)

Technically, you don't have to do this for "Sunday" on the final value if false you can just add 7, which will be the last day not explicitly coded (i.e. Sunday)

Here is the results (note, since I only coded to Tuesday, I used "4" as my last value if false so several days are being assigned:

(my date range is Jan 18 - Jan 24 - so Wednesday to Tuesday), and you can see that at least the days I coded in, they are ordered correctly.

[robinl39529461]

Thanks Jennifer.

There is still one point I don't get.

I want the order to be adapted regarding the current day, so dynamically:

On Monday: value 1 for Sunday, 2 for Saterday, ...

On Tuesday: value 1 for Monday, 2 for Sunday, ...
In your proposition (that I like by the way, thanks a lot !!!), it's about an order we set but it's a fix one as we don't use any Today.
Or maybe I missed a point in you explanation?

Thanks again

Robin

[Jennifer_Dungan · Accepted solution]

Maybe I mis-understood your question... I thought the issue was that it was changing, and you wanted it static.

Ok, you should be able to do something like the above, but now you will need to create a segment for each day of the week (since you can't use date ranges directly in calculated metrics).

Basically you need to make a segment for:

• 1 Day Ago (this can use standard date range "Yesterday")
• 2 Days Ago (this can use standard date range "2 Days Ago")
• 3 Days Ago (this can use standard date range "3 Days Ago")
• 4 Days Ago (this will need a new custom rolling date range)
• 5 Days Ago (this will need a new custom rolling date range)
• 6 Days Ago (this will need a new custom rolling date range)

and the entirety of the segment will just be the date range (as specified above)

Now, in that calculated Metric you made, you can replace the "Day of the Week = xxxx" with the segments above (you can continue to use occurences)

This should now make the Days change order in a rolling fashion based on the current day.

[robinl39529461]

Thanks a lot, you are a wizard !
I will never have though of using segment for time range selection.

We are Friday and the result look like this:

Thanks again !!!!!!

Robin

[Jennifer_Dungan]

You're welcome... and yeah.. I do a lot of "crazy" stuff to get what I need.. it comes in handy to help other people with their asks