topic Expressing Years in Quarters in QlikView

Expressing Years in Quarters

Fri, 20 Sep 2013 11:54:44 GMT

Hi There,

I'm using the following expression to express months as quarters:

'Q' & Ceil(Month(Date(Date# (<MyDate> , 'YYYYMM'),'DD-MMM-YY'))/3) as Quarter

However, where I work, our Financial year begins in November, so Q1 is (Nov, Dec, Jan).

The expression above returns Q1 as the first three month of the calendar year (Jan, Feb, Mar).. Any tips on how I can modify this to return Q1 starting in Nov?

Thanks

John

Re: Expressing Years in Quarters

tresB — Fri, 20 Sep 2013 12:23:18 GMT

Try something like:

If((Month-10)>0,Ceil((Month-10)/3),Ceil((Month-10+12)/3)) as QuarterNumber

Modify accordingly.

Re: Expressing Years in Quarters

Brice-SACCUCCI — Fri, 20 Sep 2013 12:52:09 GMT

Hi,

here's a solution without if statement, the first load is just here to generate some example data.

tmp:

LOAD

Month(MakeDate(2013, RowNo())) AS Month

AutoGenerate

12;

LOAD

Month,

'Q' & (mod(Ceil((Month - 2) / 3), 4) + 1) as Quarter

RESIDENT

tmp;

Regards,

Brice

Re: Expressing Years in Quarters

Josh_Good — Fri, 20 Sep 2013 18:18:21 GMT

You may also want to check out the post below on creating a master calendar:

Re: Creating A Master Calendar

Re: Expressing Years in Quarters

Mon, 23 Sep 2013 21:12:40 GMT

Hi All,

Thanks for the great feedback - I'm starting to find my way about a bit better

While Tresesco and Brices' code worked there are is one flaw - the first two months of Q1 are shown in the same year as Q2 , Q3 and Q4. For example, for FY2013, I need Q1 to be Nov 2012, Dec 2012 and Jan 2013. You solutions give me Nov 2013, Dec 2013 and Jan 2013. While looking at this I realised that I also need to change my year to Financial year, so that FY2013 runs from Nov 2012 to Oct 2013. Can you help with that please?

I'm trawling through the links on creating a Master Calendar but am still unsure on how to set this up - all help appreciated

Cheers

john

Re: Expressing Years in Quarters

Josh_Good — Mon, 23 Sep 2013 21:33:13 GMT

Hi John,

You can use the AddMonth function to 'shift' the date from the calendar date to the fiscal date. In your case, you need to move the date ahead 2 months (so Nov 1st, 2012) is shifted to the first day of the fiscal year.

Load

....

Year(addMonths(MyDate,2)) as FiscalYear

....

From

....

Re: Expressing Years in Quarters

Clever_Anjos — Mon, 23 Sep 2013 21:39:27 GMT

try

'Q' & Ceil(Month(addmonths(Date# (<MyDate> , 'YYYYMM'),3))/3) as Quarter

Re: Expressing Years in Quarters

jemancilla — Mon, 23 Sep 2013 22:33:13 GMT

You can make a master calendar using Brice Saccucci code and adding "FiscalYear":

//----- Example data:

tmp:

LOAD

MakeDate(2013, RowNo()) AS Date

AutoGenerate

12;

//----- Creating a master calendar

Calendar:

Load

FieldValue('Date',iterno()) As Date

,Year(FieldValue('Date',iterno())) As Year

,Year(FieldValue('Date',iterno()))+Ceil(Month(FieldValue('Date',iterno()))/10)-1 As FiscalYear

, 'Q' & (mod(Ceil((Month(FieldValue('Date',iterno())) - 2) / 3), 4) + 1) as Quarter //Brice's code

AutoGenerate 1

While(Len(FieldValue('Date',Iterno())));

//----- Good bye example data!

Drop table tmp;

Re: Expressing Years in Quarters

Tue, 24 Sep 2013 04:59:49 GMT

try this

quartername(mydate) as QuarterYear.

Re: Expressing Years in Quarters

tresB — Tue, 24 Sep 2013 05:19:21 GMT

For year, you may try like:

Load

'FY' & If(Month=11 or Month=12, Year+1, Year) as CalculatedYear

Re: Expressing Years in Quarters

Tue, 24 Sep 2013 18:09:44 GMT

Thanks to all for the help with this It's really appreciated. your suggestions fixed my problems - I'm sure I'll have many more.... Many thanks again.

John