Binning by Week - want weekending date instead of integer

[Caitlin Wrobel]

I have a simple trend line that is binned by week but instead of the integer values for the week number I would like the labels to be the weekending date (i.e. instead of "33", "8/17/2019"). I have searched high and low and think this must be an easy fix - any pointers

[Kirsten Smith (she/her)]

Try this. First, instead of a Line Chart, set this up as a Scatter Plot, using the Line Connection option, ordered by your Date column, using the "First" aggregation method.

 

Next, create a new column in your data table with an expression like:

 

case DayOfWeek([YourDate]) when 0 then [YourDate] end

 

This will create a column that is populated with the week-ending date you specify, the rest of the fields will be blank. The DayOfWeek() function returns a number, from 0 (Sunday) to 6 (Saturday), so substitute the number that makes sense with your data.

 

Then for the Labels in your Scatter Plot, select this column, again using the "First" aggregation method, and you should just get labels for the dates calculated in the column.

[Caitlin Wrobel]

Thank you for your response!  I would like the markers to be the sum for the week rather than the individual days though.  Any thoughts

 

 

 

[Kirsten Smith (she/her)]

You can use the same BinByDate() function on the x-axis, you'll just need to set it to Categorical (X-Axis/Settings) to get a graph similar to your line chart.