9 Replies Latest reply: Nov 6, 2014 6:26 PM by Marco Wedel RSS

    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

        • Re: Creating a Calender and use list box
          Enrique Colomer

          here you have an example

          • Re: Creating a Calender and use list box
            Marco Wedel

            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

            • Re: Creating a Calender and use list box
              Peter Cammaert

              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

              • Re: Creating a Calender and use list box

                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).

                  • Re: Creating a Calender and use list box
                    Peter Cammaert

                    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

                    • Re: Re: Creating a Calender and use list box
                      Marco Wedel

                      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