Creating a week dimension from dates

[#1]

I have a problem which i can't seem to solve for my application. In the data i have three lists/fields of dates:

• Date, the normal calendar (narrows the two below calendars down)
• BatchDate, which refers to dates where a operation was started
• RecordingDate, which refers to the date of the outcome of the process (an analysis for example)

The issue is that i only have one week field, which refers to Date and therefore when doing trending/plots pr. week, it selects the wrong data. This is because Date is similar to BatchDate - so when a certain week is selected, it narrows these two lists down to the same 7 days, but has twice the amount of possible dates in RecordingDate. This is since the recording of the operation could have been done the week after the operation was started (which Date and BatchDate binds to). This makes sense, but ...

... the question then is, am i able to create a new dimension such as RecordingWeek, which selects the actual dates of the selected week in RecordingDate?

Help is kindly appreciated 🙂

/ Rasmus

[sunny_talwar]

I think this should be possible. Have you given it a try yet?

[#3]

Somewhat, mostly by using a variable to select the first week day in the list and then select +6 - but that seems like a tedious way. Was hoping for some input, since I'm quite new to QV.

[sunny_talwar]

Have you tried creating a ReportingWeek field in the script?

LOAD ReportingDate,

          Week(ReportingDate) as ReportingWeek

...

and then make seleection in ReportingWeek to further drill down into a particular rerporting week?

[#5]

No, but will try that as soon as possible - thanks for the quick reply!

[#6]

I've tried placing it into my script as:

LOAD RecordingDate,

Week(RecordingDate) as RecordingWeek

But i do not get a new field after running the script?

[shaktisinghchau]

You probably need to create 3 list box out of following week fields, that way you will be able to filter data better.

LOAD     

     RecordingDate

   , Week(RecordingDate) AS RecordingWeek

   , Date

   , Week(Date) AS DateWeek

   , BatchDate

   , Week(BatchDate) AS BatchWeek

Hope this help,

[#8]

I already have a field for week which filters correctly in Date and BatchDate. It also filters in RecordingDate but in regards to the start of the process which means this will not be a calendar week but a list of dates where these operations were started. I want to create a new field which selects calendar weeks in RecordingDate, is that possible?