9 Replies Latest reply: Sep 29, 2011 7:01 AM by D.Walters RSS

    Year-to-Date issue: format

      Hi,

       

      Can anyone advise on this issue?

      I'm new to qlikview so I'm surely overlooking something here. :-)

       

      I load below data: 

       

      Cloture_PL:
      LOAD 
           Periode,
           Scenario_2, 
           Scenario_3, 
           Montant, 
           [Project Code],
           [Type PMO], 
           text([Demand ID])&'BE' as [Accounting_ID]
      FROM
      $(vQvdFolder)Cloture_PL.qvd
      (qvd);
      

       

      The format in the inputfile for field Periode is 201101;201102;201103;... referring to the Year and Month.

      I want to include a year to date calculation of sum(Montant) in a pivot with set analysis.

       

      I guess the problem is that 'Periode' is not interpreted by QV as a date.

      When trying functions like Date(), Makedate(), right(Periode,4) as Year, left(Periode,2) as Month I can't get out a usable date format.

      And the YTD calculation results in 0...

       

      Any help would be great!

      Thanks and regards,
      David

        • Year-to-Date issue: format
          Sravan Puppala

          Hello David,

           

          what do you get if you use Left(Periode,4) as Year and Right( Periode,2) as Month?

           

          Regards

          sravan

          • Re: Year-to-Date issue: format

            Hi Walters,

            What you can do is create an Year and Month field along with a period, like in the following code and then use it for your calculation.

             

             

            Cloture_PL:
            LOAD 
                 Periode,
                 left(Periode,4) as Year,
                 right(Periode,2) as Month,
                 Scenario_2, 
                 Scenario_3, 
                 Montant, 
                 [Project Code],
                 [Type PMO], 
                 text([Demand ID])&'BE' as [Accounting_ID]
            FROM
            $(vQvdFolder)Cloture_PL.qvd (qvd);
            
            

             

            Rgrds,

            Abhinava

              • Re: Year-to-Date issue: format
                Rob Wunderlich

                date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode

                 

                -Rob

                http://robwunderlich.com

                  • Year-to-Date issue: format

                    Hi all,

                     

                    Thanks a lot for your help!

                    I made a combination of above answers and loaded:

                     

                    Cloture_PL:

                    LOAD

                         date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode,

                         left(Periode,4) as PL.Year,

                         right(Periode,2) as PL.Month,

                         Scenario_2,

                         Scenario_3,

                         [Libellé compte],

                         Montant,

                         [Project Code],

                         [Type PMO],

                         text([Demand ID])&'BE' as [Accounting_ID]

                    FROM

                    $(vQvdFolder)Cloture_PL.qvd

                    (qvd);

                     

                    I'm not able to do the year to date calculation with set analysis though.

                    Below formula provides me with the sum per month, but not the year-to-date:

                     

                    =num(Sum ({$<PL.Year = {$(=only(PL.Year))},PL.Month={"<=$(=max(PL.Month))"}>}Montant))

                     

                    Any ideas?

                     

                    Thanks and best regards,

                    David

                    • Re: Year-to-Date issue: format

                      Hi all,

                       

                      Thanks a lot for your help!

                      I made a combination of above answers and loaded:

                       

                      Cloture_PL:
                      LOAD 
                           date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode,
                           left(Periode,4) as PL.Year,
                           right(Periode,2) as PL.Month,
                           Scenario_2, 
                           Scenario_3, 
                           [Libellé compte], 
                           Montant, 
                           [Project Code],
                           [Type PMO], 
                           text([Demand ID])&'BE' as [Accounting_ID]
                      FROM
                      $(vQvdFolder)Cloture_PL.qvd
                      (qvd);
                      
                      

                       

                      I'm not able to do the year to date calculation with set analysis though.

                      Below formula provides me with the sum per month, but not the year-to-date:

                       

                       

                      =num(Sum ({$<PL.Year = {$(=only(PL.Year))},PL.Month={"<=$(=max(PL.Month))"}>}Montant))
                      

                       

                      Any ideas?

                       

                      Thanks and best regards,

                      David

                        • Re: Year-to-Date issue: format

                          Hi Walters,

                           

                          for year to date you just need to omit the month expression in the set analysis expression.

                           

                          try the following code.

                          =num(Sum ({$<PL.Year = { ">=$(=only(PL.Year))", "<=$(MaxYear)"} >} Montant))
                          

                          where MaxYear=max(PL.Year), the maximum year value in PL.Year

                          PS. Remember Year to date is always calculated for the current year.

                           

                           

                          Regrds,

                          Abhinava

                    • Year-to-Date issue: format

                      Hi Walters,

                       

                      Check out this tutorial on YTD calculation using Set Analysis

                       

                      http://community.qlik.com/qlikviews/1062

                       

                      Rgrds,

                      Abhinava

                        • Re: Year-to-Date issue: format

                          Hello everyone,

                           

                          Thank you all for your help on this issue!

                          The issue is resolved by defining the same calendar and date variables as in above set analysis tutorial.

                          Then I linked the information in both PL_Cloture and Calendar with below function:

                           

                           

                          date(date#(Periode, 'YYYYMM'), 'YYYYMM') as Periode
                          
                          

                           

                          Best regards,

                          David