Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

dmelillo
Contributor III

Week Number of Quarter

I am trying to get a field that is the WEEK NUMBER of a fiscal quarter.

The wrinkle is that I have "custom" quarters built.

For instance, 2016 fiscal quarters are as expected (i.e Jan-mar = Q1), but then 2017Q1 is January 2017, 2018Q1 is Feb, Mar, Apr 2018, 20182 is May, Jun, Jul 2018 and so on

Is there any way to get the week number per quarter even when it is custom?

- dave

1 Solution

Accepted Solutions

Re: Week Number of Quarter

Well if you have a SQL Select before the LOAD statement, then you can order your close date in the SQL Select

Opps:

LOAD ...,

Autonumber( Weekstart([Close Date]), CloseQuarter) as OppCloseQuarterWeekNumber;

SQL SELECT *

FROM SFAX.dbo.Opps

Order By [Close Date];

9 Replies
MVP
MVP

Re: Week Number of Quarter

Not sure how you define your week numbers, but maybe using Autonumber().

Assuming you already created your fiscal quarters field and you have e.g. a date field:

LOAD

     DateField,

     FiscalQuartersField,

     Autonumber( Weekstart(DateField), FiscalQuartersField) as WeekNumber,

     ..

dmelillo
Contributor III

Re: Week Number of Quarter

So that did SOMETHING, but it numbered the weeks all weird, starting with 4 then jumping to 11 ... please see picture attached ... it looks like it split it into the right portions, but it didnt label them in sequential order?

this is the expression i used in the load script

Autonumber( Weekstart([Close Date]), CloseQuarter) as OppCloseQuarterWeekNumber

Capture25.PNG

Re: Week Number of Quarter

Make sure to use Order By statement

LOAD ....,

     Autonumber( Weekstart([Close Date]), CloseQuarter) as OppCloseQuarterWeekNumber

Resident ....

Order By [Close Date];

dmelillo
Contributor III

Re: Week Number of Quarter

Sunny - Appreciate this ... The load script is HUGE and contains many fields ... Will the order by effect the loading of the rest of the data?

Re: Week Number of Quarter

If you will have to create a new resident load for the purposes of creating just this new field, then you might see some performance loss. But if you already have a resident load, then performance might not go down drastically.

Re: Week Number of Quarter

Also, if you are doing this in a master calendar script, then you may have so many distinct dates and I don't think it will be causing much problems

Highlighted
dmelillo
Contributor III

Re: Week Number of Quarter

Not sure I understand complete ... lets say this is my current load script:

Opps:

LOAD ...,

Autonumber( Weekstart([Close Date]), CloseQuarter) as OppCloseQuarterWeekNumber;

SQL SELECT *

FROM SFAX.dbo.Opps;

You are suggesting I do a resident load just to create the OppCloseQuarterWeekNumber field in a seperate table that I link to the main opps table?

Something like

LOAD

     OppId,

     DateField,

     FiscalQuartersField,

     Autonumber( Weekstart(DateField), FiscalQuartersField) as WeekNumber

RESIDENT Opps

Order By [Close Date];

Re: Week Number of Quarter

Well if you have a SQL Select before the LOAD statement, then you can order your close date in the SQL Select

Opps:

LOAD ...,

Autonumber( Weekstart([Close Date]), CloseQuarter) as OppCloseQuarterWeekNumber;

SQL SELECT *

FROM SFAX.dbo.Opps

Order By [Close Date];

MVP
MVP

Re: Week Number of Quarter

David,

yes, your dates need to be sorted chronological (at least per quarter) to make this work, since the Autonumber() just assigns incremented integers per new expression result / calculated symbol (first argument) (and per quarter, second argument).

I assumed you were using a master calendar, and these are usually created using a loop, creating dates in correct order:

The Master Calendar

https://qlikviewcookbook.com/2015/05/better-calendar-scripts/

A master calendar can easily be added without any big overhead. You are usually coping with just around some thousand records.

Community Browser