topic Master Calender With Both general Quarter And Fascal Quarter in QlikView

Master Calender With Both general Quarter And Fascal Quarter

Wed, 12 Nov 2014 08:18:10 GMT

Hi ,

I am new to qlikview.

I want to design a calender which will contain quarter in listbox.

For example

When I will select the Q1 in 1st table month will show Jan,Feb, Mar in one month listbox and Mar, Apr,May in other month listbox

Thanks,

amit

Re: Master Calender With Both general Quarter And Fascal Quarter

ashfaq_haseeb — Wed, 12 Nov 2014 08:25:04 GMT

Hi,

Try this



SET vFiscalYearStartMonth = 3;
LET vStartDate = Num(YearStart(Today(), -1));
LET vEndDate = Num(YearEnd(Today()));

FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD
*,
Year(Date) AS Year, // Standard Calendar Year
Month(Date) AS Month, // Standard Calendar Month
Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name
Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter
Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month
YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear;  // Fiscal Calendar Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;

Regards

ASHFAQ

Re: Master Calender With Both general Quarter And Fascal Quarter

jagan — Wed, 12 Nov 2014 08:31:32 GMT

Hi,

Check this thread, hope it helps you.

Fiscal and Standard Calendar generation

Regards,

jagan.

Re: Master Calender With Both general Quarter And Fascal Quarter

Wed, 12 Nov 2014 08:36:57 GMT

Thank you it is working now.

can you please give a example on Presidency Load

Re: Master Calender With Both general Quarter And Fascal Quarter

MK_QSL — Wed, 12 Nov 2014 08:37:28 GMT

Set vFM = 1;

MinMaxDate:

LOAD

YearStart(Today(),-2) as MinDate,

Today() as MaxDate

AutoGenerate 1;

Let vMin = NUM(PEEK('MinDate',0,'MinMaxDate'));

Let vMax = NUM(PEEK('MaxDate',0,'MinMaxDate'));

Drop Table MinMaxDate;

Calendar:

Load

Year + If(Month>=$(vFM), 1, 0) as fYear,

Dual(SubField('$(MonthNames)',';',Mod(Month+$(vFM), 12)+1),Mod(Month+$(vFM), 12)+1) as fMonth;

Load

Date(TempDate) as Date,

Month(TempDate) as Month,

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

Year(TempDate) as Year;

Load

$(vMin) + IterNo() - 1 as TempDate

AutoGenerate 1

While $(vMin) + IterNo() - 1 <= $(vMax);

Now Create a List Box for Quarter

Create below two Text Box

=Concat(Distinct Month,',',Month)

=Concat(Distinct fMonth,',',fMonth)

Re: Master Calender With Both general Quarter And Fascal Quarter

jagan — Wed, 12 Nov 2014 08:41:23 GMT

Hi,

In the below script the bold text is preceding load

FiscalCalendar:

LOAD

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName, // Standard Calendar Month Name

Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter, // Standard Calendar Quarter

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth, // Fiscal Calendar Month

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYear; // Fiscal Calendar Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;

In the bottom most load statement we derived the date column, using this field on next load we arrived the remaining fields.

Hope this helps you.

Regards,

Jagan.

Re: Master Calender With Both general Quarter And Fascal Quarter

ashfaq_haseeb — Wed, 12 Nov 2014 08:41:55 GMT

Hi,

Check this.

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/04/preceding-load

If it answered then close this thread by selecting appropriate answers.

Regards

ASHFAQ

Re: Master Calender With Both general Quarter And Fascal Quarter

Wed, 12 Nov 2014 08:47:00 GMT

Hi thanks for reply.

But I want to know what is preceding load and with a simple example like emp or dept.

Thank you…

Re: Master Calender With Both general Quarter And Fascal Quarter

Wed, 12 Nov 2014 10:47:58 GMT

Hey guys thanks for the reply,

Can any one help me on value band?

Like if I have a salary column in a range of 7000 to 25000,

what is the syntax I have to write to show only members whose salary is greater than 10000

Re: Master Calender With Both general Quarter And Fascal Quarter

ashfaq_haseeb — Wed, 12 Nov 2014 10:50:05 GMT

Hi,

Can you create new discussion and please attach sample data and expected output.

I'm sure someone of us will help you.

Regards

ASHFAQ