Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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];
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,
..
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
Make sure to use Order By statement
LOAD ....,
Autonumber( Weekstart([Close Date]), CloseQuarter) as OppCloseQuarterWeekNumber
Resident ....
Order By [Close Date];
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?
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.
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
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];
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];
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:
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.