10 Replies Latest reply: Jun 27, 2011 8:20 AM by Roland Kunle RSS

    YTD in Cyclic Group!

      Hi

       

      I have created a cyclic group called "Date" which contains fields such as Year, Month and Week. Along with these fields I want to create one more field which calculates "YTD". So that user can select YTD from the cyclic group like the way they select Year, Month and Week.

       

      Any idea how to do that? Sample application will do good.

       

      Thanks

       

      Attitude

        • YTD in Cyclic Group!
          Nagaian Krishnamoorthy

          If YTD is a field in any of your logical files loaded in the script, you can add this to the cyclic group.

           

          If YTD is a field calculated by a chart expression, you cannot add  the calculated field to the group

            • Re: YTD in Cyclic Group!

              Hi Krishnamoorthy,

               

              Makes sense but may I know how it can be calculated in the script so that I can use it in the cyclic group.

               

               

              Temp_date:
              Load Max(Floor(Year)) AS YTD resident Calendar;
              Drop table Temp_date; //no longer needed 
              LET vCurrentYear = Peek('YTD', 0, 'Temp_date');
              


              After that can I add YTD in cyclic group as it is YTD is more less equal to start of the year to to till date and I guess Max(Floor(Year)) gives the same YTD. i,e Starting of the year to till date.

               

              Please explain your view!

               

              Thanks

               

              Attitude

                • YTD in Cyclic Group!
                  Nagaian Krishnamoorthy

                  If the YTD value has to be calculated in the script, The 'To-Date' in YTD has to be defined prior to script execution.

                   

                  One way is to default it to today's date. In this case, the reload schedule of the document has to be at least daily or as frequent as the data reload, if the relead schedule is more frequent than once per day. The YTD value calculated this way will be for the total set {1} as the user selection takes place after the script execution.

                   

                  (One may try to use the variables in the script which can be set in the command-line of releoad. In this case, the policy for using a To-date in all YTD has to be acceptable to users.)

                   

                  In all such techniques, we give up the user-interaction effects. I am not sure if adding YTD in a cyclic group will justify the effort of implementation or the functionality we forgo, especially when YTD/QTD/MTD analyses in comparison with previous YTD/QTD/MTD values can be done very elegantly using set analysis.

                   

                  However everything depends on what the user wants, though a QlikView consultant can influence/manage user expectations.

                   

                  Just my 2 cents.

              • YTD in Cyclic Group!

                Hi Attitude,

                 

                in addition to what krishnamoorthy said above: try this in your script (where you created Year, Month and Week):

                LOAD ... . .

                YearToDate( SourceDate)    AS YTD,

                . . .

                 

                HtH

                Roland

                  • Re: YTD in Cyclic Group!

                    Hi Roland

                     

                    When I see example in the help I think it just returns true are false. Are you sure that it is going to work?

                     

                     

                    Examples:
                    Assume last reload time = 1999-11-18
                    yeartodate( '1998-11-18' )
                    
                    returns false
                    
                    yeartodate( '1999-02-01' )
                    
                    returns true
                    
                    yeartodate( '1999-11-18' )
                    
                    returns true
                    
                    yeartodate( '1999-11-19' )
                    
                    returns false
                    
                    yeartodate( '1998-11-18', -1 )
                    
                    returns true
                    
                    yeartodate( '1999-11-18', -1 )
                    
                    returns false
                    
                    yeartodate( '1999-04-30', 0, 5 )
                    
                    returns false
                    
                    yeartodate( '1999-05-01', 0, 5 )
                    
                    returns true
                    
                    
                    
                    


                    Thanks

                     

                    Attitude