topic Re: How to use Quater function in QlikView

How to use Quater function

agni_gold — Thu, 21 Nov 2013 04:37:07 GMT

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

Re: How to use Quater function

Thu, 21 Nov 2013 04:48:10 GMT

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

Re: How to use Quater function

er_mohit — Thu, 21 Nov 2013 04:49:59 GMT

Try this

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

Re: How to use Quater function

Thu, 21 Nov 2013 04:51:29 GMT

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

Re: How to use Quater function

Thu, 21 Nov 2013 04:55:01 GMT

see link, hope this helps you.

QLIKView creating a calendar | elebAcraM

Re: How to use Quater function

VishalWaghole — Thu, 21 Nov 2013 05:16:17 GMT

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

Re: How to use Quater function

vinothkumarr — Thu, 21 Nov 2013 07:29:10 GMT

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

Re: How to use Quater function

agni_gold — Thu, 21 Nov 2013 10:17:03 GMT

what is the meaning of Ceil

Re: How to use Quater function

VishalWaghole — Thu, 21 Nov 2013 10:25:37 GMT

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

MK_QSL — Thu, 21 Nov 2013 10:25:45 GMT

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

agni_gold — Mon, 25 Nov 2013 05:03:00 GMT

Thanks bro very nice.