Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Creating a Calender and use list box

Hi guys,

I am new to QlikView. I want to create a calender with Year,Month & quarter with list box.

Please help me the step how to do this.

Thanks in advance

calender.png

1 Solution

Accepted Solutions
MarcoWedel

That should be easily done by renaming the field with appropriate granularity (here date) to match the fieldname (DOJ) in your excel source, or by creating a new field with a matching name.

So one additional line in the calendar code should suffice:

tabCalendar:

LOAD *,

    Date as DOJ,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

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

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MakeDate(2010)+IterNo()-1) as Date

AutoGenerate 1

While MakeDate(2010)+IterNo()-1<=Today();

hope this helps

regards

Marco

View solution in original post

9 Replies
ecolomer
Master II
Master II

here you have an example

ecolomer
Master II
Master II

I'm sorry becaus som words are in Spanish

Not applicable
Author

Hey dont you have any thing in english,

IF not can i translate it using google translate

ecolomer
Master II
Master II

I can explain you how you can obtain this.

P08.png

For the Year,

select the field (in my example is xYear), in the label Presentation, select some columns, and in the label Title, NO select Title

MarcoWedel

Hi,

one solution could be:

QlikCommunity_Thread_140823_Pic1.JPG.jpg

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

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

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MakeDate(2010)+IterNo()-1) as Date

AutoGenerate 1

While MakeDate(2010)+IterNo()-1<=Today();

hope this helps

regards

Marco

Peter_Cammaert
Partner - Champion III
Partner - Champion III

The trick is very simple but almost hidden where you won't expect it.

  1. Create a normal listbox, for e.g. a Year field.
  2. Open Object Properties->Presentation
  3. DISABLE the check mark at "Single column"
  4. Close Properties (OK)
  5. Now, move the bottom frame border of the list box UP until values are arranged side-by-side

You can also remove the title bar, to get an even cleaner look. But that's up to you.

Best

Peter

Not applicable
Author

Okay guys thanks for the reply

but can you please tell me how to link this with calender to a excel sheet like I have a field Date of Joining(DOJ).

How I can link this list box (YEAR/QUARTER/MONTH) so that when I will seclect any one the corresponding joining employee will show up..

For Example when I will click on month JAN, The Employee who joined on JAN will be displayed on other sheet Object(Table Box).

Peter_Cammaert
Partner - Champion III
Partner - Champion III

That's more or less a new question.

When loading employee data, make sure that you load their JOIN_DATE as well. While loading the Join Date (DOJ) split off the day, month and year, like this:

LOAD

:

DOJ,

Day(DOJ) AS [DOJ Day],

Month(DOJ) AS [DOJ Month],

Year(DOJ) AS [DOJ Year],

:

FROM Your EmployeeExcelFile.xlsx (...);

Now put the three new fields [DOJ Day], [DOJ Month] and [DOJ Year] on the QlikView sheet and format according to earlier instructions.

Note: this solution will only list days, months and years on which exmployees have effectively joined your company. The others won't be available because there will be no DOJ dates with those days, months or years. If you want to have a continuous selection with all dates, months and years in a certain range (for example earliest to latest Join date), then you'll have to create a master calendar based on the minimum and maximum Join date. See Marco's example above for a method to create a Master Calendar.

Best,

Peter

MarcoWedel

That should be easily done by renaming the field with appropriate granularity (here date) to match the fieldname (DOJ) in your excel source, or by creating a new field with a matching name.

So one additional line in the calendar code should suffice:

tabCalendar:

LOAD *,

    Date as DOJ,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

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

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MakeDate(2010)+IterNo()-1) as Date

AutoGenerate 1

While MakeDate(2010)+IterNo()-1<=Today();

hope this helps

regards

Marco