Date Format Formula

Discussion in 'Requests' started by bharat shah, Apr 11, 2021.

    
  1. bharat shah

    bharat shah Member


    I have created a report in which i have to enter date Manually as its for Nepali Month. for example
    Month Of SAWAN= 16-07-2020 TO 16-08-2020 AD
    Month Of BHADRA= 17-08-2020 TO 16-09-2020 Ad and so on
    How to Set it as i tried some code but cannot get it. its working for AD as show

    datejul : $$MonthOfDate:$date>=7 and $$MonthOfDate:$date<=7

    Not Working while changing Manually
    datejul: $$FromValue:"16-07-2020": $$ToValue:"16-08-2020
     


  2. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    datejul: $$FromValue:$$Date:"16-07-2020": $$ToValue:$$Date:"16-08-2020"
     
    bharat shah likes this.


  3. bharat shah

    bharat shah Member


    Code:
    
    [#Menu: Gateway of Tally]
    Add: Key Item: MONTH WISE ITEM WISE DISPATCH : W : Display : STQueryCSknl : NOT $$IsEmpty:$$SelectedCmps
    
    [Report: STQueryCSknl]
    Use : DSP Template
    Form : STQueryCSknl
    Title : "MONTH WISE DISPATCH"
    PrintSet : Report Title : "MONTH WISE ITEMWISE TOTAL SALES NOT INCLUDING ZERO VALUE BILLED"
    Variable : SV Print Orientation : String
    Variable    : ETNETNPartyMatInHarryNameLed : String
    Set        : ETNETNPartyMatInHarryNameLed    : ""
    
    Variable    : ETNETNPartyMatInHarryItemNameLedItem : String
    Set        : ETNETNPartyMatInHarryItemNameLedItem    : ""
    
    [Form: STQueryCsKnl]
    Use : DSP Template
    Parts : DB Title, STQueryCSknl
    Delete : Buttons : ExplodeFlag
    Delete : Bottom Buttons : DSPAutoColumns, BudgetAnalysis, ReportConfig
    Local: Line : DB Title : Local : Field : Name Field: Set as : "MONTH WISE ITEMWISE TOTAL SALES WITH ZERO VALUED EFFECTED"
    background : white
    Button :Print Button,Export Button,ETNETNPartyMatInHarry,ETNETNPartyMatInHarryItem
    Set Always : SV Print Orientation : "Landscape"
    
    
    
    
    [Button    : ETNETNPartyMatInHarryItem]
        Key    : Alt+I
        Title    : Stock Item
        Action    : Alter:ETNETNPartyMatInHarryItemNameLedItem   
       
    [Report: ETNETNPartyMatInHarryItemNameLedItem]
        Form    : ETNETNPartyMatInHarryItemNameLedItem
       
    [Form    : ETNETNPartyMatInHarryItemNameLedItem]
        Part: ETNETNPartyMatInHarryItemNameLedItem
        
    [Part    :ETNETNPartyMatInHarryItemNameLedItem]
        Line: ETNETNPartyMatInHarryItemNameLedItem
        
    [Line    : ETNETNPartyMatInHarryItemNameLedItem]
        Field: ETNETNmyfieldharry,ETNETNPartyMatInHarryItemNameLedItem
       
    [Field    :ETNETNPartyMatInHarryItemNameLedItem]
        Use    : name Field
        Table    : ETNETN Stock Item Harry ME
        Show Table    : Always
        Variable:ETNETNPartyMatInHarryItemNameLedItem
        Modifies    : ETNETNPartyMatInHarryItemNameLedItem
    
    [Field    :ETNETNmyfieldharry]
        Use    : name Field
        Table    : ETNETN ETNETN Stock Item Harry ME me
        Show Table    : Always
        storage : ETNETNmyharryme
       
       
    [system : udf]
    ETNETNmyharryme : string : 8654
    
    [Collection: ETNETN ETNETN Stock Item Harry ME me]
        Type: Stock Item
        
        BelongsTo: Yes
    Fetch  : Name, Parent
    
    Format : $Parent,30
    
    
    
    
    
    
    [Collection: ETNETN Stock Item Harry ME]
        Type: Stock Item
        
        BelongsTo: Yes
       Filter :ETNETN jobi1
    
    [System:formula]
    
    ETNETNjobi1:$$IsEqual:$parent:$$LocaleString:#ETNETNmyfieldharry
    
    
    
    
    
    ; select ledger starts from here
    
        
    [Button    : ETNETNPartyMatInHarry]
        Key    : Alt+V
        Title    : Select Ledger
        Action    : Alter:ETNETNPartyMatInHarryNameLed   
       
    [Report: ETNETNPartyMatInHarryNameLed]
        Form    : ETNETNPartyMatInHarryNameLed
       
    [Form    : ETNETNPartyMatInHarryNameLed]
        Part: ETNETNPartyMatInHarryNameLed
       
    [Part    :ETNETNPartyMatInHarryNameLed]
        Line: ETNETNPartyMatInHarryNameLed
       
    [Line    : ETNETNPartyMatInHarryNameLed]
        Field: ETNETNPartyMatInHarryNameLed
       
    [Field    :ETNETNPartyMatInHarryNameLed]
        Use    : name Field
        Table    : ETNETN My Ledgerrr
        Show Table    : Always
        Variable:ETNETNPartyMatInHarryNameLed
        Modifies    : ETNETNPartyMatInHarryNameLed
    
    
    [Collection: ETNETN My Ledgerrr]
        Type: Ledger
        ChildOf: $$GroupSundryDebtors
        BelongsTo: Yes
    
    
    
    
    
    
    [Part: STQueryCSknl]
    Lines : STQueryCSknlT, STQueryCSknl
    Repeat : STQueryCSknl : SItemCollCSknl
    Scroll : both
    Common Borders : Yes
    Total : OpQtyCSknl, InQtyCSknl, ConsQtyCSknl, ProdQtyCSknl, OutwQtyCSknl, ClosQtyCSknl,qtyoct,qtynov,qtydec,qtyjan,qtyfeb,qtymar,QTYTOTAL
    
    [Line: STQueryCSknlT]
    Use : STQueryCSknl
    Local : Field : Default : Type : String
    Local : Field : Default : Align : Center
    Local : Field : Default : color : white
    Local : Field : Default : background :black
    Local : Field : Default : Lines : 0
    Local : Field : ItemNameCSknl : Align : Left
    Local : Field : ItemNameCSknl : Set as: "ITEM NAME"
    Local : Field : OpQtyCSknl : Set as: "SAWAN"
    Local : Field : InQtyCSknl : Set as: "BHADRA"
    Local : Field : ConsQtyCSknl : Set as: "ASWIN"
    Local : Field : ProdQtyCSknl : Set as: "KARTIK"
    Local : Field : OutwQtyCSknl : Set as: "MANGSIR"
    Local : Field : ClosQtyCSknl : Set as: "PAUSH"
    Local : Field : qtyoct : Set as: "MAGH"
    Local : Field : qtynov : Set as: "FALGUN"
    Local : Field : qtydec : Set as: "CHAITRA"
    Local : Field : qtyjan : Set as: "BAISHAK"
    Local : Field : qtyfeb : Set as: "JESTHA"
    Local : Field : qtymar : Set as: "ASHAD"
    Local : Field : qtyTOTAL : Set as: "TOTAL"
    Border : column titles
    
    [Line: STQueryCSknl]
    Fields : ItemNameCSknl, OpQtyCSknl, InQtyCSknl, ConsQtyCSknl +
    , ProdQtyCSknl, OutwQtyCSknl, ClosQtyCSknl,qtyoct,qtynov,qtydec,qtyjan,qtyfeb,qtymar
    right field : qtytotal
    Repeat : ClosQtyCSknl
    Option : AlterOnEnter
    
    [Field: ItemNameCSknl]
    Set As : $Stockitem
    Display : Item Monthly Summary
    Variable  : StockitemName
    Border: thin Left right
    width : if $$inprintmode then 18 else 36
    Style : Normal
    
    [Field: OpQtyCSknl]
    Use : Qty Primary Field
    Style : Normal
    Format : "No Zero"
    Set as :  $QtyApr
    Border : Thin Left Right
    Width : if $$inprintmode then 7 else 8
    ;Scroll : Yes
    ;Display : Stock Summary
    
    [Field: InQtyCSknl] 
    Use : OpQtyCSknl
    Set As : $QtyMay
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    [Field: ConsQtyCSknl]
    Use : InQtyCSknl
    Set As : $QtyJun
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    
    [Field: ProdQtyCSknl]
    Use : InQtyCSknl
    Set As : $QtyJul
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    [Field: OutwQtyCSknl]
    Use : InQtyCSknl
    Set As : $QtyAug
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    [Field: ClosQtyCSknl] 
    Use : InQtyCSknl
    Set As : $QtySep
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    [Field: qtyoct]
    Use : InQtyCSknl
    Set As : $Qtyoct
    Border : Thin Right
    Background:Yellow
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    [Field: qtynov]
    Use : InQtyCSknl
    Set As : $Qtynov
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    
    [Field: qtydec]
    Use : InQtyCSknl
    Set As : $Qtydec
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    [Field: qtyjan]
    Use : InQtyCSknl
    Set As : $Qtyjan
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    
    [Field: qtyfeb]
    Use : InQtyCSknl
    Set As : $Qtyfeb
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    [Field: qtymar]
    Use : InQtyCSknl
    Set As : $Qtymar
    Border : Thin Right
    Style : Normal
    Width : if $$inprintmode then 7 else 8
    
    [Field: qtytotal]
    Use : InQtyCSknl
    Set As : $QtyTotal
    Border : Thin Right
    Style : Normal bold
    Width : if $$inprintmode then 7 else 8
    
    [Collection: Voucher CollectionCSknl]
    Type : Vouchers : VoucherType
    Child of : $$VchTypeSales
    Belongs to : yes
    Fetch  : Date,All Inventory Entries
    ;Filter : zerovaluefilter
    Filter        : PartyFilterMatLedETNETN
    
    [Collection: SItemCollCSknl]
    Source Collection : Voucher CollectionCSknl
    Walk : All Inventory Entries
    By : Stockitem : $StockItemName 
    Aggr Compute : Qtyapr : Sum : If @@CSknlSaleVr and @@dateapr Then $BilledQty Else 0
    Aggr Compute : Qtymay : Sum : If @@CSknlSaleVr and @@datemay Then $BilledQty Else 0
    Aggr Compute : Qtyjun : Sum : If @@CSknlSaleVr and @@datejun Then $BilledQty Else 0
    Aggr Compute : Qtyjul : Sum : If @@CSknlSaleVr and @@datejul Then $BilledQty Else 0
    Aggr Compute : Qtyaug : Sum : If @@CSknlSaleVr and @@dateaug Then $BilledQty Else 0
    Aggr Compute : Qtysep : Sum :If @@CSknlSaleVr and @@datesep Then $BilledQty Else 0
    Aggr Compute : Qtyoct : Sum :If @@CSknlSaleVr and @@dateoct Then $BilledQty Else 0
    Aggr Compute : Qtynov : Sum :If @@CSknlSaleVr and @@datenov Then $BilledQty Else 0
    Aggr Compute : Qtydec : Sum :If @@CSknlSaleVr and @@datedec Then $BilledQty Else 0
    Aggr Compute : Qtyjan : Sum :If @@CSknlSaleVr and @@datejan Then $BilledQty Else 0
    Aggr Compute : Qtyfeb : Sum :If @@CSknlSaleVr and @@datefeb Then $BilledQty Else 0
    Aggr Compute : Qtymar : Sum :If @@CSknlSaleVr and @@datemar Then $BilledQty Else 0
    Aggr Compute : Qtytotal : Sum :If @@CSknlSaleVr Then $BilledQty Else 0
    Sort : @@SortAlphaIncr : $partyledgername
    filter : PartyFilterMatItemETNETN
    
    
    [System: Formula]
    ; zerovaluefilter : $AllLedgerEntries.BillAllocations.Amount <> ""
    PartyFilterMatLedETNETN: $Ledgername = ##ETNETNPartyMatInHarryNameLed or $$IsSysName:##ETNETNPartyMatInHarryNameLed
    PartyFilterMatItemETNETN:$StockItem = ##ETNETNPartyMatInHarryItemNameLedItem or $$IsSysName:##ETNETNPartyMatInHarryItemNameLedItem
    
    
    CSknlPurc Vr : $$IsPurchase:$VoucherTypeName
    CSknlSale Vr : $$IsSales:$VoucherTypeName
    CSknlStock Jrnl Vr : $$IsStockJrnl:$VoucherTypeName
    
    
    dateapr :  $$FromValue:$$Date:"16-07-2020": $$ToValue:$$Date:"16-08-2020"   ;$$MonthOfDate:$date>=4and $$MonthOfDate:$date<=4
    ;datemay : $$FromValue:$$Date:"17-08-2020": $$ToValue:$$Date:"16-09-2020"     ;$$MonthOfDate:$date>=5 and $$MonthOfDate:$date<=5
    ;datejun   : $$MonthOfDate:$date>=6 and $$MonthOfDate:$date<=6
    ;datejul    : $$MonthOfDate:$date>=7 and $$MonthOfDate:$date<=7
    ;dateaug  : $$MonthOfDate:$date>=8 and $$MonthOfDate:$date<=8
    ;datesep  : $$MonthOfDate:$date>=9 and $$MonthOfDate:$date<=9
    ;dateoct  : $$MonthOfDate:$date>=10 and $$MonthOfDate:$date<=10
    ;datenov  : $$MonthOfDate:$date>=11 and $$MonthOfDate:$date<=11
    ;datedec  : $$MonthOfDate:$date>=12 and $$MonthOfDate:$date<=12
    ;datejan  : $$MonthOfDate:$date>=1 and $$MonthOfDate:$date<=1
    ;datefeb  : $$MonthOfDate:$date>=2 and $$MonthOfDate:$date<=2
    ;datemar  : $$MonthOfDate:$date>=3 and $$MonthOfDate:$date<=3
    not working sir
    i
     

    Attached Files:



  4. Amit Kamdar

    Amit Kamdar Administrator Staff Member


    Aggr Compute : Qtyapr : Sum : If @@CSknlSaleVr and ($Date >= $$Date:"16-07-2020" AND $Date <= $$Date:"16-08-2020") Then $BilledQty Else 0
     
    bharat shah likes this.


  5. bharat shah

    bharat shah Member


    thank you sir its working
     


Share This Page