How to defind current date into Month/Quarter/Fiscal Year?

[ivanyang25]

Dear QS Experts

I am new to Qlik Sense and I have question regarding to date in Qlik Sense Desktop

In my raw data there is a column named 'POD Date' stores data like '2017/7/20' , '2017/4/3', '2017/6/8' etc

I want to create a dimension in master item to define these date into relative Month/Quarter/Fiscal Year

For month I can use expression like =num (Month([POD Date])) to get the result I want (7,4,6)

How to acheive the same goal for Quarter (i.e Q1,Q2) / Fiscal Year (i.e 1718)?

PS: I want to solve this problem in front end instead of writing script from back end

Thank you for your advise in advance

Ivan

[robert99]

Hi

I usually use quartername. This gives Jan-Mar 2017 etc. Its a dual so is sortable by number. But other options are quarter end or start date

Fiscal Year I always do in script. In fact all calendars I do in script. And if you use data manager its done automatically using derived fields

Refer a good explanation on this

Fiscal Year

One recent example I did. Just replace DateCanonn and CallInDate with POD Date

Set vFM = 10 ;  // year Oct to Sept  First month of fiscal year

Calendar:  //inc fiscal years

//level 5

Load

Dual(fYear-1 &'/'& fYear, fYear) as YearFin,          // Dual fiscal year

Dual(Month, fMonth)                as MonthFin,           // Dual fiscal month for sorting

*

;

//level 4

Load

Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

*

;

Calendar: 

//level 3

load

DateCanon ,

Month(DateCanon) as Month,

Year(DateCanon) as Year,

Day (DateCanon) as Day,

weekday (DateCanon) as DayMonEtc,

weekend (DateCanon,0,-2) as WeekEnding  ,

Week (DateCanon) AS WeekNum,

Monthstart (DateCanon) as MthYear ;

//level 2

Load Date (MinDate+iterno()) as DateCanon While iterno() <= MaxDate - MinDate ;

//level 1

Load Min(CallInDate)-1 as MinDate, Max(CallInDate)+30 as MaxDate Resident SCCall ;

[ivanyang25]

Robert

Thank you for the feedback

As I am a front end user thus it is a little bit difficult for me to read below material

Fiscal Year

Should I add the script in into below button or in Data Load Editior?

There are several datas in my raw data (order date/ship date/ POD date etc) and I need POD date for reference to show fiscal year and quarter in sheet 1 and Ship date in sheet 2. Which date in below script example should I replace with ship date/POD date?

On the other hand, how should we work quarter out?

It will be highly appreciated if you could share me a test apps for reference

Thanks

[robert99]

Try this

Put all this script in at the end using data load editor

This will create a master calendar for every day from a minimum starting date to an end date

And link it to POD Date

let me know if it works or not

Set vFM = 10 ;  // year Oct to Sept  First month of fiscal year set as appropriate >> required level 4

//level 5 reads in level 4 then drops these level 4 fields

Calendar:  //inc fiscal years

Load

Dual(fYear-1 &'/'& fYear, fYear) as YearFin,          // Dual fiscal year

Dual(Month, fMonth)                as MonthFin,           // Dual fiscal month for sorting

*                                                                              // Picks up fields in level 4

;

//level 4 reads in level 3 then drops these level 3 fields

Load

Year + If(Month>=$(vFM), 1, 0) as fYear,           // Numeric fiscal year

Mod(Month-$(vFM), 12)+1        as fMonth,          // Numeric fiscal month

*                                                                           // picks up fields in level 3

;

  //level 3 reads in level 2 then drops these level 2 fields

MASTERCalendar:

load

[POD Date],     ///LINK field

Month([POD Date]) as Month,   //PUT IN CALENDAR FIELDS AS REQUIRED

Year([POD Date]) as Year,

Day ([POD Date]) as Day,

Week ([POD Date]) AS WeekNum,   //if required

Monthstart ([POD Date]) as MthYear ;  //if required

//level 2 reads in level 1 then drops these level 1 fields

Load Date (MinDate+iterno()) as [POD Date] While iterno() <= MaxDate - MinDate

;

//level 1 //start of load .. must link to another table

Load Min([POD Date])-1 as MinDate,

Max([POD Date])+30 as MaxDate //change days (30) as required

Resident >>>your table Name ;  // link to the appropriate table that contains POD date