4 Replies Latest reply: Mar 11, 2014 11:52 AM by Roland Kunle RSS

    Filtering pivot with date variables

    Paolo Guidolin

      Hi I'm new in Qlikview and I need help to create a pivot table from the follow excel table

       

      C_CLIENTEC_LINEA_FATC_LISTINO_RIGADATA_FATTURADATA_RIF_DOCGroupmeseP_SC_CAN1_CNTP_SC_CAN2_CNTP_SC_CAN3_CNTP_SC_COMP_CNTP_SC_DERIVA_COMPLETA_CNT
      C000000900500008515/01/200907/01/20095880,000,19000,10,450001666
      C000000900500008515/01/200912/01/20095880,000,19000,10,450001666
      C000000900500008515/01/200913/01/20095880,000,19000,10,450001666
      C000000900500008515/01/200914/01/20095880,000,19000,10,450001666
      C000000900500008531/01/200915/01/20095880,000,19000,10,450001666
      C000000900500008531/01/200916/01/20095890,000,19000,150,480557129
      C000000900300008531/01/200920/01/20095860,000,190000,38889074
      C000000900300008531/01/200928/01/20095860,000,190000,38889074
      C000000900300008515/02/200903/02/20095860,000,190000,38889074
      C000000900300008515/02/200911/02/20095860,000,190000,38889074
      C000000900300008528/02/200917/02/20095860,000,190000,38889074
      C000000900300008515/03/200903/03/20095860,000,190000,38889074
      C000000900300008915/03/200903/03/20095870,000,190000,38889074
      C000000900300008915/03/200911/03/20095870,000,190000,38889074
      C000000900300008931/03/200917/03/20095870,000,190000,38889074

       

      in qlikview document I added two slider object for filterig by DATA_RIF_DOC

      In the pivot i need to have C_CLIENTE as dimension and as expression for the max Group field for the selection period the values of fields

       

      P_SC_CAN1_CNT

      P_SC_CAN2_CNT

      P_SC_DERIVA_COMPLETA_CNT

      MIN(DATA_RIF_DOC)

      MAX(DATA_RIF_DOC)

       

      I attach a sample document with data

       

      I have tried using FirstSortedValue.

       

      is the correct way?

       

      Can someone help me?

       

      thanks

        • Re: Filtering pivot with date variables

          Ho Paolo,

          didn't catch exactly what you want, so check my guesses.

          Hope this points into right direction.

          Roland

          • Re: Filtering pivot with date variables
            Ashwani Kumar

            Hi ,

             

            Firstly you need to upload the excel file ( which contains data only) in the QV and the create Pivot table there.

             

            Happy Qliking

            • Re: Filtering pivot with date variables
              Paolo Guidolin

              Maybe I have not explained properly my problem. For example, i set date variable in the slider from 10/06/2009 to 13/01/2009

               

              C_CLIENTEC_LINEA_FATC_LISTINO_RIGAmeseDATA_FATTURADATA_RIF_DOCGroupP_SC_CAN1_CNTP_SC_CAN2_CNTP_SC_CAN3_CNTP_SC_COMP_CNTP_SC_DERIVA_COMPLETA_CNT
              C00000090030000890,0015/06/200903/06/20095870,190000,38889074
              C00000090030000890,0015/06/200910/06/20095870,190000,38889074
              C00000090030000890,0030/06/200916/06/20095870,190000,38889074
              C00000090030000890,0030/06/200924/06/20095870,190000,38889074
              C00000090030000891,0015/07/200930/06/20095870,190000,38889074
              C00000090030000890,0015/07/200908/07/20095870,190000,38889074
              C00000090030000890,0015/07/200914/07/20095870,190000,38889074
              C00000090050000850,0015/01/200907/01/20095880,19000,10,450001666
              C00000090050000850,0015/01/200912/01/20095880,19000,10,450001666
              C00000090050000850,0015/01/200913/01/20095880,19000,10,450001666
              C00000090050000850,0015/01/200914/01/20095880,19000,10,450001666
              C00000090050000850,0031/01/200915/01/20095880,19000,10,450001666
              C00000090050000850,0031/01/200916/01/20095890,19000,150,480557129

               

              and in the pivot i need to get only 1 record like

               

              C_CLIENTEGroupP_SC_CAN1_CNTP_SC_CAN2_CNTP_SC_CAN3_CNTP_SC_COMP_CNTP_SC_DERIVA_COMPLETA_CNTmin
              DATA_RIF_DOC
              max
              DATA_RIF_DOC
              C00000095880,19000,10,45000166610/06/2009

              13/01/2009

               

              in which for the max  Group values in the selection period(588) are shows the corresponding values of

              P_SC_CAN1_CNT
              P_SC_CAN2_CNT
              P_SC_CAN3_CNT
              P_SC_COMP_CNT

              P_SC_DERIVA_COMPLETA_CNT

              and the min e max values of field DATA_RIF_DOC

               

              thanks for your help