topic Re: Calculating quarters in QlikView

Calculating quarters

vijetas42 — Wed, 12 Mar 2014 10:47:36 GMT

Hi,

How should i calculate quartes from given date.I have tried but it's giving me quarter for each month but I want quarters according to fiscal month

Re: Calculating quarters

MK_QSL — Wed, 12 Mar 2014 10:53:21 GMT

Check enclosed file...

Re: Calculating quarters

tresB — Wed, 12 Mar 2014 10:53:37 GMT

Try like:

Load *,

'Q' & Celi(fMonth/3) as FiscalQtr;

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

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

Load Year(Date) as Year, // Your standard master calendar

Month(Date) as Month

From <>;

Re: Calculating quarters

vijetas42 — Wed, 12 Mar 2014 10:55:11 GMT

Can you just share script as I am unable to open your qvw?

Re: Calculating quarters

Wed, 12 Mar 2014 10:57:07 GMT

Try this:

Load *,

'Q' & Ceil(Month/3) as Quarter

Cheers,

Lucas

Re: Calculating quarters

MK_QSL — Wed, 12 Mar 2014 11:00:39 GMT

Sales:

Load * Inline

[

InvoiceDate,Sales

01/01/2013, 100

20/01/2013, 200

01/02/2013, 300

28/03/2013, 400

03/04/2013, 150

04/05/2013, 250

05/05/2013, 300

06/06/2013, 100

06/07/2013, 120

07/07/2013, 180

08/08/2013, 60

09/09/2013, 80

10/10/2013, 280

01/11/2013, 300

01/12/2013, 165

10/01/2014, 140

15/01/2014, 160

20/02/2014, 190

];

MinMaxDate:

Load

Min(InvoiceDate) as MinDate,

Max(InvoiceDate) as MaxDate

Resident Sales;

Let vMinDate = NUM(Peek('MinDate',0,'MinMaxDate'));

Let vMaxDate = NUM(Peek('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

TempDate:

Load

Date($(vMinDate) + RowNo() - 1) as InvoiceTempDate

AutoGenerate $(vMaxDate) - $(vMinDate)+1;

InvoiceMasterCalendar:

LOAD

InvoiceTempDate as [InvoiceDate],

Day(InvoiceTempDate) as InvoiceDay,

WeekDay (InvoiceTempDate) as InvoiceWeekday,

Week(InvoiceTempDate) as InvoiceWeek,

Month(InvoiceTempDate) as InvoiceMonth,

Num(Month(InvoiceTempDate)) as InvoiceMonthNO,

Year(InvoiceTempDate) as InvoiceYear,

'Q' & ceil(month(InvoiceTempDate) / 3) as InvoiceQuarter,

Date (monthstart (InvoiceTempDate) , 'MMM YYYY') as InvoiceMonthYear,

if(num(month(InvoiceTempDate))<=3,Year(InvoiceTempDate)-1&'-'&right(Year(InvoiceTempDate),2),Year(InvoiceTempDate)&'-'&right(year(InvoiceTempDate)+1,2)) as FinancialYear,

if(num(month(InvoiceTempDate))<=3,Year(InvoiceTempDate),year(InvoiceTempDate)+1) as FinancialYear1,

if(num(month(InvoiceTempDate))>=1 and num(month(InvoiceTempDate))<=3,'Q4',

if(num(month(InvoiceTempDate))>=4 and num(month(InvoiceTempDate))<=6,'Q1',

if(num(month(InvoiceTempDate))>=7 and num(month(InvoiceTempDate))<=9,'Q2',

if(num(month(InvoiceTempDate))>=10 and num(month(InvoiceTempDate))<=12,'Q3')))) as FinancialQuarter

RESIDENT TempDate

ORDER BY InvoiceTempDate ASC;

DROP TABLE TempDate;

Re: Calculating quarters

er_mohit — Wed, 12 Mar 2014 11:19:54 GMT

Try this code in script here change TempDate with your Date

'Q'&If(Month(QuarterStart(TempDate,4))='1' ,4,Div(Month(QuarterStart(TempDate,4)),3)) as QuarterStartsapr,