5 Replies Latest reply: Oct 29, 2012 10:39 AM by Nandha Kumar RSS

    For Loop in qlikview

    Nandha Kumar

      Hi,

       

       

      I
      am new to software development and Qlikview. I have a pivot table in my dashboard;
      I am using this table for multiple selections. For example I have prompt selection
      in the name of yearly, half yearly, Quarterly and monthly. If I select the
      monthly prompt a multi box will appear adjacent to the prompt. Under the
      monthly box list of month will be displayed, if I select the corresponding
      month by say April the pivot table should show me the April data; if I select
      the Dec month pivot should show Dec data. Like this it’s applicable for
      quarterly (Q1=Jan+Feb+Mar) and half yearly (H1=Jan+Feb+Mar+Apr+May+Jun).

       

       

      I
      want to use only one pivot table for this scenario. Now I am using multiple pivot
      tables (copy pasting it) by created a variable and call that variable under conditional
      layout properties.

       

       

      Is
      there a solution for this where I can use only one pivot table for the entire
      scenario?

       

       

      Thanks in advance

       

      Regards,

      Nandha

        • Re: For Loop in qlikview

          Nandha,

          Si entendi bien, deseas que tu tabla Pivot aparezca con los datos del mes seleccionado? Es correcto?

          Estas utilizando un "Master Calendar" en tu script?

          La solución esta en crear un calendario principal, en el cual puedes configurar todas tus combinaciones de fechas, intervalos, ytd, etc.... y tenerlos como campo seleccionable. Así tus tablas traeran los datos por las fechas, mes, ytd, Q, Y, etc como lo deseas.

          Patrick

            • Re: For Loop in qlikview
              Nandha Kumar

              Dear Patrick,

               

              I'm not using the calendar script I'm using the Inline function for this prompt selection. Now i am using 22 pivot table to get the table data. I want this data to be displayed in single table and change accordingly when i select the prompt. Pls find the script that I used to edit script i used for this below

               

              Month:


              Load * Inline [

              Es_Month, Eg_Month,Half,Half_Num, month_num,Quarter,Quarter_Num

              Mes1, Ene,H1,17, 1,Q1,1

              Mes2, Feb,H1,17, 2,Q1,1

              Mes3, Mar,H1,17, 3,Q1,1

              Mes4, Abr,H1,17, 4,Q2,2

              Mes5, May,H1,17, 5,Q2,2

              Mes6, Jun,H1,17, 6,Q2,2

              Mes7, Jul,H2,18, 7,Q3,3

              Mes8, Ago,H2,18, 8,Q3,3

              Mes9, Spe,H2,18, 9,Q3,3

              Mes10, Oct,H2,18, 10,Q4,4

              Mes11, Nov,H2,18, 11,Q4,4

              Mes12, Dic,H2,18, 12,Q4,4

              ];


              Menus:

              LOAD * INLINE [

                  Menu, Menu_Num

                  Anual, 1

                  Semestral, 2

                  Trimestral, 3

                  Mensual, 4

              ];

            • Re: For Loop in qlikview

              Nandha,

              Well, as far as I can understan, you have a or various tables with your data (not the inline table Month) with your values and also one or various dates fieds. Those fields must be used to create a calendar or diferent fields with your dates type (month, year, Q, etc.)

              For example: You have a table with your sales per days like this

               

               

              DateArticle
              CantityUnit PriceTotal
              01-10-2012Pen525125
              05-10-2012Paper21020
              15-11-2012Light10550
              18-11-2012Pen825200
              01-12-2012Paper61060

               

              Now, in your script when you load your data, you can create new fields based on your original data table, in this case the Date fields.

              For example (read all the date format in Help menu)

               

              month(Date) as Month,

              Year(Date) as Year,

              'Q' & ceil(Date / 3) AS C_Quarter,

                        'Q' & ceil(Date/ 3) & '-'& Y AS C_QuarterYear,

                        week(Date) AS C_Week,

                        weekday(Date + 2) AS C_Weekday,

                        weekName(Date AS C_Semanas,

                        day(Date AS C_Day,

                        date(Date,'DD/MM/YY') AS C_DateDDMMYY,

                        QuarterName("Date") as C_Trimestres,

                        Monthname("Date") as C_Mes,

                        Month(today()) as C_MesActual,

                        inmonthtodate("Date", $(vToday), 0) * -1 AS C_MTD,

               

              So, doing that you can have a List Box and select the month, week, Q, etc as you wich and your Pivot table will display the data for such month, week etc... If you wich to change the display title of the dimensions, only put the field in the subject and it will change also.

               

              On the other way, you will maybe need a master calendar, but for that, I suggest you to look around in the forum and documentation for calendar subject.

               

              Hope this help.