Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
Thank you it is working now.
can you please give a example on Presidency Load
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)
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.
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
Hi thanks for reply.
But I want to know what is preceding load and with a simple example like emp or dept.
Thank you…
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
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