Week Number of Quarter

[Anonymous]

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

[swuehl]

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,

     ..

[Anonymous]

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

[sunny_talwar]

Make sure to use Order By statement

LOAD ....,

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

Resident ....

Order By [Close Date];

[Anonymous]

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?

[sunny_talwar]

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.

[sunny_talwar]

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

[Anonymous]

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];

[sunny_talwar · Accepted solution]

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];

[swuehl]

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.