topic Re: Creating a week dimension from dates in QlikView

Creating a week dimension from dates

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Creating a week dimension from dates

sunny_talwar — Fri, 10 Jun 2016 15:17:25 GMT

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

Re: Creating a week dimension from dates

Fri, 10 Jun 2016 15:23:15 GMT

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.

Re: Creating a week dimension from dates

sunny_talwar — Fri, 10 Jun 2016 15:25:22 GMT

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?

Re: Creating a week dimension from dates

Fri, 10 Jun 2016 15:28:16 GMT

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

Re: Creating a week dimension from dates

Sat, 11 Jun 2016 09:07:40 GMT

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?

Re: Creating a week dimension from dates

shaktisinghchau — Sun, 12 Jun 2016 06:43:41 GMT

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,

Re: Creating a week dimension from dates

Sun, 12 Jun 2016 10:57:38 GMT

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?