topic Re: Quarter should get start from April in QlikView

Quarter should get start from April

sarfaraz_sheikh — Tue, 16 Jun 2015 04:19:45 GMT

Dear All,

I want my quarter to be start from Q1 April,may,Jun Q2 July,Aug,Sep ..........etc

I am using below expression for calculating Quarters

Q' & Ceil(Month(INWD_RECV_DATE)/3) AS Quarter

But Q1 starts from Jan, Feb,Mar and so on......What shoukd i have to do to start my quarter from april onwards.

Sarfaraz

Re: Quarter should get start from April

qlikmsg4u — Tue, 16 Jun 2015 04:51:09 GMT

Hi Sarfaraz,

Try like this

=Q' & Ceil(Month(AddMonths(Date,-3))/3)

or you can use

if(month(Date)='Apr','Q1',

if(month(Date)='May','Q1',

if(month(Date)='Jun','Q1',

if(month(Date)='Jul','Q2',

if(month(Date)='Aug','Q2',

if(month(Date)='Sep','Q2',

if(month(Date)='Oct','Q3',

if(month(Date)='Nov','Q3',

if(month(Date)='Dec','Q3',

if(month(Date)='Jan','Q4',

if(month(Date)='Feb','Q4',

if(month(Date)='Mar','Q4')))))))))))) as Quarter,

Re: Quarter should get start from April

PrashantSangle — Tue, 16 Jun 2015 05:03:15 GMT

Hi,

Try this,

='Q' & if(Ceil(Num(month(DateField))/3)>1,Ceil(Num(month(DateField))/3)-1,Ceil(Num(month(DateField))/3)+3)

Regards

Re: Quarter should get start from April

prashantbaste — Tue, 16 Jun 2015 05:18:10 GMT

Hi Sarfaraz,

You can either 1] calculate

or 2] create Inline of Months:Quatrer mapping and use in your application.

See below example for your reference:

1] Calculate Quarter for Month as Below:

If(Num(Month(DateColName))>=4 and Num(Month(DateColName))<=6,'Q1',

If(Num(Month(DateColName))>=7 and Num(Month(DateColName))<=9,'Q2',

If(Num(Month(DateColName))>=10 and Num(Month(DateColName))<=12,'Q3','Q4')))

2] Mapping of Quarter to Months as Below:

LOAD * INLINE [

MonthName, Quarter

Jan, Q4

Feb, Q4

Mar, Q4

Apr, Q1

May, Q1

Jun, Q1

Jul, Q2

Aug, Q2

Sep, Q2

Oct, Q3

Nov, Q3

Dec, Q3

];

Join

Calender:

LOAD DateColName,

Left(MonthName(DateColName),3) as MonthName

From Calanderqvd.qvd(qvd);

======================================================

Hope this will resolve your problem.

Regards,

Prashant P Baste

Re: Quarter should get start from April

Tue, 16 Jun 2015 05:25:03 GMT

Hi Sarfaraz,

try like this it's a simple way...

if(month(Date)='Apr','Q1',

if(month(Date)='May','Q1',

if(month(Date)='Jun','Q1',

if(month(Date)='Jul','Q2',

if(month(Date)='Aug','Q2',

if(month(Date)='Sep','Q2',

if(month(Date)='Oct','Q3',

if(month(Date)='Nov','Q3',

if(month(Date)='Dec','Q3',

if(month(Date)='Jan','Q4',

if(month(Date)='Feb','Q4',

if(month(Date)='Mar','Q4')))))))))))) as Quarter,

Ramya.

Re: Quarter should get start from April

Tue, 16 Jun 2015 05:31:53 GMT

Does in your dashboard business year starts from April of a year till March of next year ?

Example April 2014 - March 2015

Re: Quarter should get start from April

Tue, 16 Jun 2015 05:32:05 GMT

Hi,

Try the following code:

Dates:

Load

Date(Date,'YYYYMMDD') as DateId,

Date,

Date - MakeDate(Year(Date),01,01) + 1 AS DayInYear,

Year(Date) AS Year,

Month(Date) AS MonthName,

Month(Date) & ' ' & Year(Date) AS MonthNameYear,

if((Ceil(Month(Date))-3)<=0,(Ceil(Month(Date))+9) ,(Ceil(Month(Date))-3))as Month,

Week(Date) AS WeekInYear,

Day(Date) AS DayInMonth,

WeekDay(Date) AS DayInWeek,

QuarterName(Date) AS QuarterNameYear,

SubField(QuarterName(Date),' ',1) AS QuarterName,

'Q' &if( (Ceil(Month(Date)/3)-1)=0,4,(Ceil(Month(Date)/3)-1)) as Quarter;

Load

AddMonths(today(),12) - recno() AS Date

Autogenerate (AddMonths(today(),12) - MakeDate(2012,01,01));

Regards,

Jemimah

Re: Quarter should get start from April

jagan — Tue, 16 Jun 2015 05:37:54 GMT

Hi,

Check this link

Fiscal and Standard Calendar generation

Regards,

Jagan.