Collection of Date Range(Period) between two dates

Discussion in 'Free Source Codes' started by Devendra_Rawat, Aug 22, 2020.

    
  1. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    If we have two dates like

    FromDate : 01/04/2020
    EndDate : 30/06/2021

    and between these two dates
    there are 15 months
    there are 5 quarters
    there is 1 year
    there are two half years

    Now I generate a month collection which gives
    April -2020
    May-2020
    June-2020

    and a I generate a Quarter Collection which gives
    Q1-2020-21
    Q2-2020-21
    Q3-2020-21
    Q4-2020-21
    Q1-2021-22

    and similarly a HalfYear Collection and a Years Collection between any two dates.

    Question is : Is there any practical use of these collections/tables

    Kindly share your views
     


  2. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    If you are having periodic collections, then it is simple to compute the values within the range.

    If you a full collection of 15 months, then you will have to compute multiple values based on date range. Either way works, depends on one's proficiency in computing the range values.

    Having periodic collections will be more efficient.
     
    Devendra_Rawat likes this.


  3. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    There is Default Period Collection in Tally Code.

    We can use that, I have used it once.. But not very sure how it works.. I have developed my own period collection as mentioned above, but I am short of ideas, where to use it practically.

    I need a practical scenario.
     


  4. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    You can use it in columnar report, where, as per user's election, calculations and computations of values are made on periodic basis.

    Also you can use in Line level, where, different period values are required.
    e.g.
    1st Mainline --------- Sales of current year " INR 150000/-
    2nd Sub-line ---------(last week - 25000/-), (Last month - 61000/-), (Last Qtr - 102000/-)....etc etc
     
    Jaydeep Shah likes this.


  5. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    Thanks,

    Where were you recently.. seems you have developed in depth understand of collection during this missing time:):)
     


  6. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    stuck at Client's site and native place in Gujarat for 2 months in lockdown.... was away from TDL for almost 3.5 months.....

    Thoda thoda gyaan mil gaya........ sab members ka queries solve kartein kartein......... :);)
    (gained some knowledge here and there, from solving Member's queries)
     


  7. Devendra_Rawat

    Devendra_Rawat Well-Known Member



    Great.. Gyan baantne se gyaan bahut teji se badhta hai..


    I appreciate the way you help members...
     


  8. Taufeeq Shaikh

    Taufeeq Shaikh Active Member


    Devendraji,

    I would like to know about the collection that you had made as i need in my UDF Report..

    (i.e.) i want to set Values in my udf for the ITC Claimed in the Month of F.Y.



    upload_2021-1-9_20-39-1.png
     


  9. Devendra_Rawat

    Devendra_Rawat Well-Known Member


    ok.. shortly
     


  10. Devendra_Rawat

    Devendra_Rawat Well-Known Member



    Ok..

    How it works:
    1. You to have pass starting date and end date to the function TxoSetMonthsRange
    2. Dates should be 1st of any month and 30/31 of any month ( definitely it could be 28th/29th of Feb also)
    3. Function will use a list variable , which will be Data source in the period collection,
    4. Now you will have to make different collections (e.g. monthly, quarterly or yearly etc .. ) taking the collection mention in point 3. as source
    5. Use those collections (in your case monthly collection)
    6. Form here which displays period collection is just to show one of the usage, practically we will have to use date fields in collection to compute the month or quarter field in the same collection

    ..Demo Attached.

    Code:
    
    
    
      
    [#Menu: Gateway of Tally]
      
        Add        : Key Item    : Before    : @@locQuit    : "Taxo Period Colllection"    : P : Alter : TaxoChangePeriod : NOT $$IsEmpty:$$SelectedCmps  AND NOT $$SelectedCmps < 1 ;;Call : ImportCostCate  
        Add      : Key Item :     Before    : @@locQuit :BLANK
    
    
    [Report: TaxoChangePeriod]  ;; Auto Report
              
                Form    : TaxoChangePeriod
              
                Local   : Field: MV Title         : Info       : $$LocaleString:"Change Period"
                Title    : $$LocaleString:"Change Period"
                Auto    : No
              
        [Form: TaxoChangePeriod]
                Part    : TaxoPeriodColl
                Set        : svMonthCollUpdated : No
                Width    : 40 % Screen
                No Confirm    : No
              
            [Part    :    TaxoPeriodColl]
                Line        : TaxoPeriodTitle
                Lines      : TaxoSV DateRange, TaxoPeriodSelectionTtile, TaxoPeriodSelection
              
                 [Line: TaxoPeriodTitle]
                    Field    : MV Title
                    Space Bottom    : 0.5
    
                [Line: TaxoSV Date Range]
    
                    Field : Medium Prompt, TaxoFromDate, Short Prompt, TaxoToDate
                    Local : Field : Medium Prompt : Info : @@DateFromFormat
                    Local : Field : Short Prompt : Info : @@DateToFormat
                    Border    : thin Bottom
                    Space Bottom    : 1
                  
                    [Field: TaxoFromDate]
    
                        Use         : Short Date Field
                        Set Always    : Yes
                        Format      : Short Date
                        Width        : 10
            
                    [Field: TaxoToDate]
    
                        Use         : Short Date Field
                        Format      : Short Date
                        Set Always    : Yes
                        Width        : 10
                        Validate    : $$IsEmpty:$$Value OR $$Value >= #TaxoFromDate
                        On            :  Accept    : yes    : Call    : TxoSetMonthsRange : #TaxoFromDate : #TaxoToDate
                        On            :  Accept    : Yes    : Field Accept
                      
                  
                [Line    : TaxoPeriodSelectionTtile]
                        Use        : TaxoPeriodSelection
                        Local : Field    : Medium Prompt    : Inactive: Yes
                      
                        Local: Field    : Default : Delete    : Table
                        Local : Field    : TaxoMonthColl    : Info    : " Month Table"
                        Local : Field    : TaxoQrtrColl    : Info    : " Qrtr Table"
                        Local : Field    : TaxoYearColl    : Info    : " Year Table"
                        Local: Field: Medium Prompt     : Info    : "Show Tables"
              
      
                [Line    : TaxoPeriodSelection]
                    Field    : Medium Prompt,   TaxoMonthColl, TaxoQrtrColl, TaxoYearColl
                    Local    : Field: Medium Prompt : Info    : "Tables"
                  
                  
                      
                      
    
                    [Field    : TaxoMonthColl]
                        Use            : Name Field
                        Table        : TxoMonthColl
                      
                        Width        : 15
                        Show Table    : Always
                    ;    Set Always    : Yes
                      
                    [Field    : TaxoQrtrColl]
                        Use            : Name Field
                        Table        : TxoQrtrColl
                      
                        Width        : 15
                        Show Table    : Always
                    ;    Set Always    : Yes
                      
                  
                    [Field    : TaxoYearColl]
                        Use    :     Name Field
                        Width        : 15
                        Table    : TxoYearColl
                        Show Table    : Always
                        Dynamic    :""
                      
      
    
    
    
    [Function: TxoSetMonthsRange]
        Parameter    : StartDate        : Date
        Parameter    : EndDate        : Date
      
        Variable    : cvMonth        : String
        Variable    : cvYear        : String
        Variable    : KeyIndex      : Number    : 1
      
        Variable    : vMonthNumber    : Number
        Variable    : vMonthString    : String
        Variable    : vtd2aFinMnth    : String
      
        Variable    : vYearNumber    : String
        Variable    : vtd2aFinYear    : String
        Variable    : vFinYrStart    : Date
        Variable    : vFinYrEnd        : Date
      
        Variable    : strFinYrStrt    : String
        Variable    : strFinYrEnd    : String
         
        Variable    : StrFinQtr        : String
        Variable    : vtd2FinQtrBeg    : Date
        Variable    : vtd2FinQtrEnd    : Date
      
    
      
        0002    : Log:##EndDate
        0003    : Log:##StartDate
        0004    : List Delete Ex:  TxoMonthsInRange
        0005    : Set            : KeyIndex        : 1
        0010     : For Range : FinMonth    : Date : ##StartDate    : ##EndDate : 1 : "Month"
        0015     :     List Add Ex:  TxoMonthsInRange
    ;;    0020    :    Log    : ##FinMonth
        0080    :    Set    : vMonthNumber    : $$MonthOfDate:##FinMonth
        0085    :    Set    : vFinYrStart    : $$FinYearBeg:##FinMonth:$StartingFrom:Company:##SVCurrentCompany
        0090    :    Set    : vFinYrEnd        : $$FinYearEnd:##FinMonth:$StartingFrom:Company:##SVCurrentCompany
    ;;    0095    :    Log : ##vFinYrStart
        0100    :    Set    : strFinYrStrt    : ($$YearOfDate:##vFinYrStart)
        0110    :    Set    : strFinYrEnd    : ($$YearOfDate:##vFinYrEnd)
        0120    :    Set    : vtd2aFinYear    : ##strFinYrStrt +"-"+ ##strFinYrEnd
    ;;    0130    :    Log : ##vtd2aFinYear
        0140    :    Set    : vMonthString    : $$ZeroFill:##vMonthNumber:2
    ;;    0150    :    Log : ##vMonthNumber
        0160    :    Set    : vMonthString    : $$TaxoGetMonthNo:##vMonthString
    ;;    0170    :    Log : ##vMonthString
      
        0230    :    Set    : vtd2aFinMnth    : $$String:##vtd2aFinYear+"-"+$$String:##vMonthString
        0240    :    Set    : vYearNumber    : $$YearOfDate:##FinMonth
      
        0245    :     Set    : StrFinQtr        :  If ##vMonthString in ("01", "02","03") then "Q-01" Else If ##vMonthString in ("04", "05","06") then "Q-02" Else If ##vMonthString in ("07", "08","09") then "Q-03" Else "Q-04"
        0250    :    Set    : StrFinQtr        : $$String:##vtd2aFinYear+"-"+##StrFinQtr  
        0270    :    Set : vtd2FinQtrBeg : $$TxoQuarterBeg:##FinMonth
        0280    :     Set : vtd2FinQtrEnd    : $$TxoQuarterEnd:##FinMonth  
    
        0410     :    Set : TxoMonthsInRange[##KeyIndex].MonthName    : ##vtd2aFinMnth
        0420     :    Set : TxoMonthsInRange[##KeyIndex].MonthStart    : $$MonthStart:##FinMonth
        0430     :    Set : TxoMonthsInRange[##KeyIndex].MonthEnd        : $$MonthEnd:##FinMonth
        0435     :    Set : TxoMonthsInRange[##KeyIndex].QrtrName        : ##StrFinQtr  
        0440     :    Set : TxoMonthsInRange[##KeyIndex].QtrStart        : ##vtd2FinQtrBeg
        0450     :    Set : TxoMonthsInRange[##KeyIndex].QtrEnd        : ##vtd2FinQtrEnd  
        0455     :    Set : TxoMonthsInRange[##KeyIndex].YearName        : ##vtd2aFinYear
        0460     :    Set : TxoMonthsInRange[##KeyIndex].YearStart    : ##vFinYrStart
        0470     :    Set : TxoMonthsInRange[##KeyIndex].YearEnd        : ##vFinYrEnd  
        0500    :    Set : KeyIndex        : ##KeyIndex + 1
        0550    :    Log    : $$String:##vtd2aFinMnth
        0600     : End For
    
      
      
    
    [Function    : TaxoGetMonthNo ]
        Parameter    : pMonth : String
        Returns    : String
        Variable    : vNameMonth  : String
       
        ; 070    :    Log:$$String:##pMonth
        ; 080    :    Log:"Date Printed"
         130    :    Do If:  ##pMonth="01"  : Set: vNameMonth :"10"    ;"10-January"
         140    :    Do If:  ##pMonth="02"  : Set: vNameMonth :"11"    ;"11-February"
         150    :    Do If:  ##pMonth="03"  : Set: vNameMonth :"12"    ;"12-March"
         160    :    Do If:  ##pMonth="04"  : Set: vNameMonth :"01"    ;"01-April"
         170    :    Do If:  ##pMonth="05"  : Set: vNameMonth :"02"    ;"02-May"
         180    :    Do If:  ##pMonth="06"  : Set: vNameMonth :"03"    ;"03-June"
         190    :    Do If:  ##pMonth="07"  : Set: vNameMonth :"04"    ;"04-July"
         300    :    Do If:  ##pMonth="08"  : Set: vNameMonth :"05"    ;"05-August"
         310    :    Do If:  ##pMonth="09"  : Set: vNameMonth :"06"    ;"06-September"
         320    :    Do If:  ##pMonth="10"  : Set: vNameMonth :"07"    ;"07-October"
         330    :    Do If:  ##pMonth="11"  : Set: vNameMonth :"08"    ;"08-November"
         340    :    Do If:  ##pMonth="12"  : Set: vNameMonth :"09"    ;"09-December"
    ;     350    : Log:##vNameMonth
         360    : Return    : ##vNameMonth
    
    [Function    : TaxoGetCalenderMonthNo ]
        Parameter    : pMonth : String
        Returns        : Number
        Variable    : vNameMonth  : String
       
        ; 070    :    Log:$$String:##pMonth
        ; 080    :    Log:"Date Printed"
         130    :    Do If:  ##pMonth="10"  : Set: vNameMonth :"01"   ;"10-January"
         140    :    Do If:  ##pMonth="11"  : Set: vNameMonth :"02"   ;"11-February"
         150    :    Do If:  ##pMonth="12"  : Set: vNameMonth :"03"   ;"12-March"
         160    :    Do If:  ##pMonth="01"  : Set: vNameMonth :"04"   ;"01-April"
         170    :    Do If:  ##pMonth="02"  : Set: vNameMonth :"05"   ;"02-May"
         180    :    Do If:  ##pMonth="03"  : Set: vNameMonth :"06"   ;"03-June"
         190    :    Do If:  ##pMonth="04"  : Set: vNameMonth :"07"   ;"04-July"
         300    :    Do If:  ##pMonth="05"  : Set: vNameMonth :"08"   ;"05-August"
         310    :    Do If:  ##pMonth="06"  : Set: vNameMonth :"09"   ;"06-September"
         320    :    Do If:  ##pMonth="07"  : Set: vNameMonth :"10"   ;"07-October"
         330    :    Do If:  ##pMonth="08"  : Set: vNameMonth :"11"   ;"08-November"
         340    :    Do If:  ##pMonth="09"  : Set: vNameMonth :"12"   ;"09-December"
    ;     350    : Log:##vNameMonth
         360    : Return    : $$String:##vNameMonth
       
    
    ;-----------------------------------------------------------------------------------------
    
    
    [Object    :TxoMonthsInRange]
    
      
    [System: Variable]
        List Variable: TxoMonthsInRange
    
    [Variable: TxoMonthsInRange]
        Variable: MonthName          : String
        Variable: MonthStart      : Date
        Variable: MonthEnd        : Date
        Variable: QrtrName          : String
        Variable: QtrStart        : Date
        Variable: QtrEnd        : Date
        Variable: YearName          : String
        Variable: YearStart        : Date
        Variable: YearEnd        : Date
    
    /*
        This Function is to Set the Financial Years from the Current Company - Year Wise.
    */
    
      
          
    [Function: TxoQuarterNoOfDate]
      
        Returns        : Number
        Parameter    : pDate        : Date
        Variable    : vQuarter    : Number
      
        001    :    If    :    $$MonthOfDate:##pDate >0
        002    :        Do If    : ($$MonthOfDate:##pDate In(4,5,6))   : Set : vQuarter :1
        003    :        Do If    : ($$MonthOfDate:##pDate In(7,8,9))   : Set : vQuarter :2
        004    :        Do If    : ($$MonthOfDate:##pDate In(10,11,12)): Set : vQuarter :3
        005    :        Do If    : ($$MonthOfDate:##pDate In(1,2,3))      : Set : vQuarter :4
        001n :  End If
        007    : Return    : ##vQuarter
    
    [Function: TxoQuarterBeg]
      
        Returns        : Date
        Parameter    : pDate        : Date
        Variable    : vQBegStr    : String
        Variable    : vQBeg        : Date
        Variable    : vYear        : Number
      
    ;    005        : Log    :"----------------------------Quarter Beginig ---------------------"
    ;    006        : Log    :##pDate
        011    :    If    :    $$MonthOfDate:##pDate >0
        012    :        Set        : vYear    : $$YearOfDate:##pDate
        013    :        Do If    : ($$MonthOfDate:##pDate In(4,5,6))   : Set : vQBegStr :"01/04/"+$$String:##vYear
        014    :        Do If    : ($$MonthOfDate:##pDate In(7,8,9))   : Set : vQBegStr :"01/07/"+$$String:##vYear
        015    :        Do If    : ($$MonthOfDate:##pDate In(10,11,12)): Set : vQBegStr :"01/10/"+$$String:##vYear
        016    :        Do If    : ($$MonthOfDate:##pDate In(1,2,3))      : Set : vQBegStr :"01/01/"+$$String:##vYear
    ;    016a:        Log    :##vQBegStr + " Quarter Beg Str"
        016b:        Set        : vQBeg:$$Date:##vQBegStr
       
        017 :  End If   
        018    : Return    : $$Date:##vQBeg
          
    
    [Function: TxoQuarterEnd]
      
        Returns        : Date
        Parameter    : pDate        : Date
        Variable    : vQEnd        : Date
        Variable    : vQEndStr    : String
        Variable    : vYear        : Number
      
    ;    005        : Log    :"----------------------------Quarter End ---------------------"
    ;    006        : Log    :##pDate
      
        011    :    If    :    $$MonthOfDate:##pDate >0
        012    :        Set        : vYear    : $$YearOfDate:##pDate
        013    :        Do If    : ($$MonthOfDate:##pDate In(4,5,6))   : Set : vQEndStr :"30/06/"+$$String:##vYear
        014    :        Do If    : ($$MonthOfDate:##pDate In(7,8,9))   : Set : vQEndStr :"30/09/"+$$String:##vYear
        015    :        Do If    : ($$MonthOfDate:##pDate In(10,11,12)): Set : vQEndStr :"31/12/"+$$String:##vYear
        016    :        Do If    : ($$MonthOfDate:##pDate In(1,2,3))      : Set : vQEndStr :"31/03/"+$$String:##vYear
    ;    016a:        Log    :##vQEndStr + " Quarter End Str"
        016b:        Set    : vQEnd :$$Date:##vQEndStr
        017 :  End If
        018    : Return    : ##vQEnd
    
    
      
    ;----------------------------------------------------------------------------------------------------------------------------------------------      
    [Collection    : TaxoPeriodType]
        Title        : Periodicity
        List Name    : "Month", "Quarter",  "Year"
        Fetch        : Name
        Format        : $Name, 12
      
    
    [Collection: TxoMonthsInRange]
            Data Source   : Variable    : TxoMonthsInRange
            Fetch    :    *.*
          
          
    [Collection: TxoMonthColl]
            Title                : $$LocaleString:"Availabe Months"
            Sub Title             : "Months", "Start Date", "End Date"
            Source Collection    : TxoMonthsInRange
          
            By        : Year        : $YearName
            By        : Month        :$MonthName  
            Compute : Period    :$MonthName  
            Compute : FromDate  :$$Date:$$String:$MonthStart
            Compute : EndDate    :$$Date:$$String:$MonthEnd  
            Compute : Quarter    :$QrtrName  
            Compute : QtrBeg    :$$Date:$$String:$QtrStart  
            Compute : QtrEnd       :$$Date:$$String:$QtrEnd  
            Compute : YearBeg    :$$Date:$$String:$YearStart  
            Compute : YearEnd     :$$Date:$$String:$YearEnd  
                  
            Format    : $Month, 12
            Format    : $FromDate, 10
            Format    : $EndDate, 10
    
            Sort    : @@Default     :$$Date:$FromDate, $Month
          
    [Collection: TxoQrtrColl]
            Title                : $$LocaleString:"Availabe Quarters"
            Sub Title    : "Quarters", "Start Date", "End Date"
            Source Collection    : TxoMonthsInRange
          
            By        : Year         :$YearName  
            By        : Quarter     :$QrtrName  
            Compute    : QtrBeg     :$$Date:$QtrStart  
            Compute    : QtrEnd        :$$Date:$QtrEnd  
          
          
            Format    : $Quarter, 8
            Format    : $QtrBeg, 10,
            Format    : $QtrEnd, 10,
            Sort    : @@Default : $$Number:Year
          
          
      
    [Collection: TxoYearColl]
            Title                : $$LocaleString:"Availabe Years"
            Sub Title             : "Years", "Start Date", "End Date"
            Source Collection    : TxoMonthsInRange
          
            By        : Year         :$YearName
          
            Compute    : YearBeg     :$$Date:$YearStart  
            Compute    : YearEnd      :$$Date:$YearEnd  
            Format    : $Year, 12
            Format    : $YearBeg, 10
            Format    : $YearEnd, 10
          
    
     

    Attached Files:

    Taufeeq Shaikh likes this.


  11. Himanshu-2002

    Himanshu-2002 Active Member


    Thank You Sir Devendra Sir.. It helped me too
     


  12. Himanshu-2002

    Himanshu-2002 Active Member


    I still don't know much about variable as a data source But I am trying to learn it...
     


  13. Himanshu-2002

    Himanshu-2002 Active Member


    And Sir One More Question... Why you set your name as "Garima" ? Why you don't use your name?
     


  14. Taufeeq Shaikh

    Taufeeq Shaikh Active Member


    Garima is the name of Organisation / Firm in which Devendraji is associated with...
     
    Devendra_Rawat likes this.


  15. Himanshu-2002

    Himanshu-2002 Active Member



  16. NIRMALGHORAWATIN

    NIRMALGHORAWATIN New Member


    Devendra ji / (Garima)

    A Statutory Compliance Register - Form & Report - can be generated based on this with UDF - Date of Filing / Due Date of Filing & Acknowledgement No
    say for example ::
    1. Monthly / GST / ESI / PF returns / payments etc -
    2. Quarterly - TDS Returns
    3. Half-yearly - GST returns for Composition Scheme dealers
    4. Yearly - Audit / IT Return etc
     
    Devendra_Rawat likes this.


  17. Sai Vineeth

    Sai Vineeth Active Member


    I Recently worked with auto column report there also we use similar kind of methodology.
    Period Collection/Period Template also does the same (some extra options available - day, week and fortnight)
    based on SVPeriodicity Value (it can be Month,Day,Week,Fortnight,3 Month,6 Month, Year)

    Period Collection or your collection can be used in any report (that is affected by date) and make column report/auto column report, which is very useful when doing reconciliation or want to see numbers and do comparative analysis.
    I mostly use this in voucher Statistics report and trial Balance, balance sheet or any stat report, and use default auto column report
    upload_2023-8-3_20-37-17.png
     
    Devendra_Rawat likes this.


Share This Page