10 Replies Latest reply: Nov 25, 2013 12:03 AM by Agnivesh Kumar

How to use Quater function

How can i use Quater funtion if i have mnths in one column and year in other column.

• Re: How to use Quater function

Hi,

Date,

'Q' & Ceil(Month(Date)/3) As Quarterly

from tableName

Regards,

Ashutosh

• Re: How to use Quater function

Try this

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

• Re: How to use Quater function

what is the meaning of Ceil

• Re: How to use Quater function

Hi agnivesh,

If you value is 3.2 then Ceil function will return 4

and

Floor function will return 3.

Thanks and Regards,

Vishal Waghole

• Re: How to use Quater function

ceil(x [ , base [ , offset ]])

Rounding of x upwards to the nearest multiple of base with an offset of offset. The result is a number.

Examples:

ceil( 2.4 ) returns 3

ceil( 2.6 ) returns 3

ceil( 3.88 , 0.1 ) returns 3.9

ceil( 3.88 , 5 ) returns 5

ceil( 1.1 , 1 , 0.5 ) returns 1.5

• Re: How to use Quater function

hi

try this

//************************* Example one*******************************

SET STARTDATE = ’01-01-2006′; //fill in your start date
LET ENDDATE = date(today());
[TEMP_DATE]:
date( date#(‘\$(STARTDATE)’,'DD-MM-YYYY’)-1 + recno() ,’DD-MM-YYYY’) as DATE
AUTOGENERATE (date#(‘\$(ENDDATE)’) – date#(‘\$(STARTDATE)’))+1;
[DATETABLE]:
DATE,
Year(DATE) as YEAR,
Month(DATE) as MONTH,
Week(DATE) as WEEK,
WeekDay(DATE) as WEEKDAY,
Day(DATE) as DAY,
Year(DATE) & right(’00′ & week(DATE),2) as YYYYWW,
Year(DATE) & right(’00′ & num(Month(DATE)),2) as YYYYMM,
Year(DATE) & ceil(num(Month(DATE))/3) as YYYYK,
‘Quarter’ & ceil(num(Month(DATE))/3) as QUARTER
RESIDENT [TEMP_DATE];

DROP TABLE [TEMP_DATE];

//************************* Example two*******************************

We need the highest and lowest date values from the fact table and place these values into variables that we use to create a Calender table. Search for the date field (in this example we will call the field FactDate) in the fact table. FactDate will be the connecting field to the Calendar table. Sort the fact table by typing the following at the end of the select statement before the semicolon:

* ORDER BY FactDate ASC;
* Your fact table should look something like this:

//************************* Fact table*********************************

Facts:
FactName,
FactFreight,
FactShipperID,
FactDate
FROM facts ORDER BY FactDate ASC;

(Remember that you should only have one date / time field in the fact table, the master calendar table will contain all the date formats.)

* Create a new tab in the script editor and call it Calendar.
* Create a new variable for the first date by typing the following script statement:
LET varMinDate = Num(Peek(‘FactDate’, 0, ‘Facts’));

Where FactDate is the linking Date field, 0 is the first row of the table and Facts is the FactTable. When there is more then one fact table, for example Facts2, your statement should look like this:
LET varMinDate = Num(rangemin(Peek(‘FactDate’, 0,’Facts’), Peek(‘FactDate’, 0,’Facts2′)));

* Create a second variable for the last date by typing the following statement:
LET varMaxDate = Num(Peek(‘FactDate’, -1, ‘Facts’));

* Create a third variable for the last date by typing the following statement:
LET vToday = num(today());

* Create a new table by typing the following script into the script editor:

//************************Temporary Calendar***************************

//Create all days in the range from varMinDate to varMaxDate
TempCalendar:
\$(varMinDate)+Iterno()-1 AS Num,
Date(\$(varMinDate)+Iterno()-1) AS TempDate
AUTOGENERATE 1 WHILE \$(varMinDate)+Iterno()-1<= \$(varMaxDate);

* Once we have created all the dates needed, we can start creating a Master Calendar table where we create all the date fields needed.

//**************************Master Calendar******************************

//Building the master calendar with most date dimensions
MasterCalendar:
week(TempDate) as Week,
Year(TempDate) as Year,
Month(TempDate) as Month,
Day(TempDate) as Day,
Year2date(TempDate)*-1 as CurYTDFlag,
Year2date(TempDate,-1)*-1 as LastYTDFlag,
Date(monthstart(TempDate), ‘MMM-YYYY’) AS MonthYear,
Week(TempDate)&’-'&Year(TempDate) as WeekYear,
Weekday(TempDate) as WeekDay
resident TempCalendar order by TempDate Asc;

* Before we reload the script, we need to get rid of the TempCalendar table used for creating the dates:
Drop Table TempCalendar;

• Re: How to use Quater function

hi

see link, hope this helps you.

QLIKView creating a calendar | elebAcraM

• Re: How to use Quater function

Thanks bro very nice.

• Re: How to use Quater function

Hi Agnivesh,

'Q' & Ceil(Num(MONTH)/3) As Quarter

from   TableName;

hope it will work for u.

Thanks

Vishal Waghole

• Re: How to use Quater function

Hi Agnivesh,

1. You can create the Quarter using Inline also

* INLINE

[
Month, Quarter
Jan, Q1
Feb, Q1
Mar, Q1
Apr, Q2
May, Q2
Jun, Q2
Jul, Q3
Aug, Q3
Sep, Q3
Oct, Q4
Nov, Q4
Dec, Q4
]

;

By this way you can create the Quarter for Fiscal Calendar also.

2. You can create in the script using "if" condition.

Hope this helps!

Please get back in case more help required.

--

Vinoth