Master Calender With Both general Quarter And Fascal Quarter

[#1]

Hi ,

I am new to qlikview.

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

For example

Q1

Q2

Q3

Q4

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

[ashfaq_haseeb]

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

[jagan]

Hi,

Check this thread, hope it helps you.

Fiscal and Standard Calendar generation

Regards,

jagan.

[#4]

Thank you it is working now.

can you please give a  example on Presidency Load

[MK_QSL]

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)

[jagan · Accepted solution]

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.

[ashfaq_haseeb]

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

[#8]

Hi thanks for reply.

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

Thank you…

[#9]

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

[ashfaq_haseeb]

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