Forum Discussion
Last 8 weeks - In order
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.
rg72 hi rob what ou will have to do is create an index field and then sort that date range field by the index, i.e if you look on your modelling table, you can sort different fields by other fields using sort by.
that should resolve it
vanessafvgYou make it sound so easy :)
Whichever field I choose, I get the same error.
If you have a 'Rob is a dummy' step-by-step guide that would be great :)
lol
rg72 ok this will be to do with modelling your data, however the index has to be unique to each part of the range, so you need have only one value for each range value - make sense?
ie, #
1 jan
2 feb
1 jan
3 mar
2 feb
hi, rg72, gooranga1, vanessafvg
this is easily solved by using a calendar table.
You create a column with the desired format "dd / mm - dd / mm" and indexing correctly through the calendar table itself.Do you already try that?
Is it possible to send a sample of data to help you?
Thanks all.
I must admit to be getting confused by calendar tables etc - as mentioned I'm pulling directly from Service Now, so only have a single table of data in the background.
Rfranca- Thanks for the offer. I have dropped you a PM.
gooranga1- Thanks for the suggestion. I did try this, and may return to it if I can't get any of the others to work.
vanessafvg- I'm much more of a dummy than that :D Again with modelling, I only have 1 table, so I guess this is where my problem is.
Why don't you just chnage your text formula to return the first date of the week in yyyy-MM-dd format?
Date.ToText(Date.StartOfWeek([sql_date],Day.Monday),"yyyy-MM-dd")
That would be in the correct order then.
If you format your text differently and just use the start date of the week it will sort ok.
Date.ToText(Date.StartOfWeek([sql_date],Day.Monday),"yyyy-MM-dd")
If you change the format of your start date of week it should sort ok.
Date.ToText(Date.StartOfWeek([sql_date],Day.Monday),"yyyy-MM-dd")
