Solved: Last 8 weeks - In order - Page 2

rg72 · ‎01-15-2018

Hi all.

I'm building a graph to show a rolling 8 weeks of tickets resolved by a support team. The data is being pulled directly from service now via odbc.

Using help on here provided to others, I've got the graph to show the last 8 weeks of data, however the axis is sorted in numerical order, rather than date order. I've been wracking my brain on how to re-order the axis, but no joy.

The code used to generate is ...

"Week", each Date.ToText(Date.StartOfWeek ([sys_created_on], Day.Monday), "dd/MM") & "-" & Date.ToText(Date.EndOfWeek([sys_created_on],Day.Monday),"dd/MM"))

Obviously this is using date to text, hence the ordering issue, but I'm not sure how to proceed.

I think I can cut out the 2nd part of the formula (ie the "- xx/xx' part) and just have an effective week commencing date of dd/MM, but it's then how I convert that to be able to get the correct sort order that I'm suck at.

Any help would be greatly appreciated.

TIA

Rob

gooranga1 · ‎01-15-2018

Hi @rg72,

@Rfranca is correct, this is far easier with a date dimension however using your customised field of "dd/MM" will be problematic as you cannot gurantee that that combination is unique for that week over several years which is typically what you get in a date dimension. For example 26/12-01/01 occurs in 2011 and 2016.

If you are using just a small amount of data not spanning more than a few years you won't get this problem however. Using the last 90 days of my date dimesnion I created a "dummy" dimension table using your format so that it's order correctly.

Create StartDateOfWeek and Custom.2 columns in your original table.

Copy that table as a new table and group the new table as so.

Then join the new table and original table together on start date of week

Then sort your custom column in the new table.

Then you can use the custom2 field as your axis

And it will ordered correctly.

I am not a big fan of date formats like that and I would much prefer to see a date as the start of week in "yyyy-MM-dd" format.

View solution in original post