Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

agni_gold
Valued Contributor II

How to use Quater function


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

10 Replies
Not applicable

Re: How to use Quater function

Hi,

     You can add your quarter in a load script and  use as other columns.

     Write this in your load script.

    Load

          Date,

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

    from tableName

Regards,

Ashutosh

er_mohit
Honored Contributor II

Re: How to use Quater function

Try this

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

Not applicable

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]:
LOAD
date( date#(‘$(STARTDATE)’,'DD-MM-YYYY’)-1 + recno() ,’DD-MM-YYYY’) as DATE
AUTOGENERATE (date#(‘$(ENDDATE)’) – date#(‘$(STARTDATE)’))+1;
[DATETABLE]:
LOAD
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:
LOAD FactID,
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:
LOAD
$(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:
load TempDate as FactDate,
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;

Save and reload your script

Not applicable

Re: How to use Quater function

hi

see link, hope this helps you.

QLIKView creating a calendar | elebAcraM

vishalwaghole
Valued Contributor II

Re: How to use Quater function

Hi Agnivesh,

Please try this

Load   MONTH,

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

from   TableName;

hope it will work for u.

Thanks

Vishal Waghole

vinothkumarr
New Contributor II

Re: How to use Quater function

Hi Agnivesh,

1. You can create the Quarter using Inline also

 

LOAD

* 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

agni_gold
Valued Contributor II

Re: How to use Quater function

what is the meaning of Ceil

vishalwaghole
Valued Contributor II

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

Community Browser