Unlock Actionable Insights with Bottom-Up Analytics – A Personalized Demo Offer

 Most businesses rely on readymade reports from various ERPs, which often follow a top-down approach. While this method starts with aggregates and drills down to transactions, it can overlook crucial details and limit flexibility in analytics. At Turbodata, we take a different path: a bottom-up approach. By capturing raw transactional data from multiple systems and rolling it up using advanced business logic, we provide insights that are deeper, clearer, and more actionable.

---

Top-Down vs. Bottom-Up: A Road Trip Analogy

• Top-Down: Imagine planning a road trip by estimating your fuel needs based solely on the destination. This might ignore critical factors like terrain or mileage.
• Bottom-Up: Our approach calculates fuel needs by factoring in distance, terrain, and car efficiency—ensuring a more precise and reliable journey.

Similarly, the bottom-up approach enables:

• Flexibility: Generate aggregates tailored to your analytical needs.
• Integration: Seamless alignment with multiple software systems due to raw data availability.
• Custom Allocations: Allocate percentages dynamically based on group-level hierarchies.
• Pattern Recognition: Identify trends and patterns that are invisible in top-down views.
• Efficiency Gains: Provide a clearer and more accurate picture to optimize operations.

---

Common Business Challenges Addressed

Here’s how our approach addresses real-world problems faced by businesses:

1. Where has the cash gone?

   • By analyzing ledgers, party ledgers, groups, and time views, we uncover hidden cash flow patterns.

2. Why is sales stagnating?

   • Our system evaluates item, party, and time views simultaneously to pinpoint bottlenecks.

3. Sales is increasing, but gross profits aren’t improving.

   • We analyze item, party, group, stock group, and time views to understand the root cause.

---

Let Us Demonstrate the Difference

To illustrate the power of our bottom-up analytics, we’d love the opportunity to conduct a personalized demo using a sample ERP system. This session will showcase how we can:

• Integrate seamlessly with your existing systems.
• Provide detailed insights that drive smarter decisions.
• Enable flexibility and scalability in your reporting processes.

Please feel free to reach out to schedule a demo or ask any questions.

Contact Information:

• Name: Apoorv Chaturvedi
• Email: support@mndatasolutions.com; support@turbodatatool.com
• Phone: +91-8802466356

We’re excited about the opportunity to collaborate and deliver actionable insights tailored to your business.

Warm regards,
Apoorv Chaturvedi
Team Turbodata

---

Let me know if you’d like further customization or additional materials to accompany this message!

Why a Datawarehouse over transaction system

 Query from the customer

Why should I keep a datawarehouse when I already have a transaction system?

The reasons are as follows:

a.) Consolidation requirements: One could have multiple data sources from which reports could need to be developed. The data sources could be from outside of core ERP also. Here the datawarehouse could be of help.

b.) Faster seamless reporting: If the reports involve high amount of data transactions then it could put a lot of load of Transaction systems. In such a case it is best to run the queries from a different copy of the transaction system.

c.) Minimal load on the transaction system[issue of On fly calculations]: many source systems use parametrized reports for generating the output. For generating consolidated analytics one could require the complete details. For example complete ledger movements in Tally are there in Ledger voucher reports. To get the output of each ledger one has to send the ledgername as a parameter in the ledger voucher report. Say there are 500 ledgers then we would have to hit the ledgervoucher report 500 times. One would require to hit the ledgervoucher report only once. For the same coding the business logic of the software could be required in the datawarehouse.

d.) Increase in number of users accessing the reports: many a times the reports need to be seen by large number of end users. One cannot do so over the transaction systems as it can lock the transaction system. The same needs to be done on a datawarehouse.

e.) I am already using the cloud based viewing platform, why should I use a datawarehouse: If one is using a cloud based datawarehouse[using google query and other technologies] then the following are the scenarios for using an on premise datawarehouse:

• Need for security

 

•   Need to consolidate data from multiple data sources
• Need to give greater number of users access to information: the users could be internal and external
•  Need for developing customized mobile apps and report applications
•  Lower and more predictable costs: one does not have to pay per user basis. 

What are the cloud based datawarehouses: there are examples like snowflake, googlequery and many others.

When should I use  datawarehouse?

If one is having more than 15 power users for reports then one should start thinking of using a datawarehouse.

What are the cons of using an on site datawarehouse?

• A certain fixed costs are involved
•  Trained manpower including IT personnel might be required.

ROI of a datawarehouse? This is slightly tricky. The ROI needs to be calculated as follows:

a.) Saving of time in consolidarion of sheets

b.) Reducing the manual errors in spreadsheets

c.) Enhanced operations efficiency -this needs to be calculated differently

d.) Enhanced benefit of getting the right information to right people quickly.

For more benefits of the usage of the datawarehouse

Name; Apoorv Chaturvedi

email: support@turbodatatool.com;support@mndatasolutioons.com

Indiamart page: https://www.indiamart.com/mnbusinessintelligenceindia/

 

Difference between datawarehouse and a transactional system

 Many personnel believe that datawarehouse is simply a copy of the trasactional system. However there are some key differences between the transaction system and datawarehouse.

a.) Datawarehouse can be used for consolidation of data across multiple data sources. Datawarehouse can have two layers, one an ODS[operational data store] and the second is  star schema layer[denormalization layer to minimize the joins]

Why is the normalization layer required?

a.) Normalization layer helps consolidate the data from multiple data sources in a single database layer.

b.) Used for incremental data loads. One does not need to do a full load after normalization layer. Only inserts, updates and deletes will do.

c.) The on fly calculations of the software  are done in the normalized layer.

Thus the ODS layer is different from the transaction layer.

Why not simply use the transaction system code?

a.) The transaction system copy will have only the base data

b.) The on fly calculations of the software will not be there in the copy  of the transaction system. The business logic would need to be worked out.

The purpose of star schema.

The purpose of the star schema is to reduce the joins. Lower the number of joins the faster shall be the speed of query processing.

What about snowflake schema? snowflake schema is normalization layer. It is done when the dimension tables are very large.

Where do we start?

Understand what is a datawarehouse and see if it fits into your scheme of things.

Name: Apoorv Chaturvedi

email: support@mndatasolutions.com;support@turbodatatool.com

Phone:+91-8802466356

Increase the report efficiency multifold -cost center analysis

 Problem statement: many customers use Tally for cost center analysis. The customers have a need to get profit and loss, cash flows statements for cost centers across multiple companies. In order to get the details from Tally itself the end client needs to work with the cost center voucher report such as follows:

The xml request is as follows:

<ENVELOPE>

  <HEADER>

    <VERSION>1</VERSION>

    <TALLYREQUEST>Export</TALLYREQUEST>

    <TYPE>Data</TYPE>

    <ID>ccvouchers</ID>

  </HEADER>

<BODY>

<DESC>

<STATICVARIABLES>

        <EXPLODEFLAG>Yes</EXPLODEFLAG>

            <SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>

<Costcentrename>Aerocitymohali</Costcentrename>

                  </STATICVARIABLES>

      <TDL>

        <TDLMESSAGE>

<REPORT NAME="ccvouchers" ISMODIFY="Yes">

  <ADD>Set : SV From Date:"20170401"</ADD> 

  <ADD>Set : SV To Date :"20250430" </ADD> 

                                       

  <ADD>Set : ExplodeFlag : Yes</ADD>

                </REPORT>

        </TDLMESSAGE>

      </TDL>

</DESC>

</BODY>

</ENVELOPE>

Here the given report needs to be accessed cost center by cost center to get the complete details.

Tally also has a number of variables whose value can be set.

Thereafter comes the issue of handling inventory valuations by cost center. This report export will also give the inventory movement details. These issues need to be handled.

The extract takes place only for one company.

Bottlenecks:

a.) Works  for only one company

b.) A number of variables can be adjusted.

c.) If there are a large number of cost center then the report needs to be accessed multiple times. One has to do the same repeatedly. say one has 50 cost centers then the loop has to be run 50 times.

Getting the cost of sales value: Tally in its profit and loss report gives the cost of sales details as follows:

The cost of sales needs to be derived too at the cost centre level. Hence the inventory valuations need to be also done at the cost center level or the inventory valuations need to be done perpetually using the weighted average rate.

In order to get the inventory valuation at the item level, we need to access the stock vouchers report.

Here again we need to get the data repeatedly from Tally. If there are 50 stock items then we need to hit the stock vouchers report 50 times.

Our resolution:

a.) Extract raw data from Tally multiple companies.

b.) Get raw cost center data for ledger and inventory movements.

c.) The complete group hierarchy should be enabled. We do not need just the ledgerprimary but also the other child attributes for our analysis.

d.) Tally extract through json should happen only once. The data processing should happen in Powerbi or SQL Server.

Say a client has 2 companies with  40 ledgers, 10 stock items then our productivity should increase by atleast 800 times(40*10*2).

The given cost center ledger movement has the complete group and ledger hierarchy across unlimited companies.

The debit and credit movements have also been specified in the same. From here we shall get the complete ledger movements.

Inventory movements we have  the second view as follows:

Thereafter we have consolidated the ledger and inventory movements onto a single table. In that single table we have done cash inflow, cash outflow, profit and loss and other analysis.

Limitations: The cost center cost of sales would included only those inventory movements where godown is involved. One needs to calculate the perpetual weighted average rate.

Alternatively we can get the weighted average rate on a perpetual basis from the inventory movements as shown below:

For getting the comprehensive data for profit and loss and cash flow statements please contact the following:

Name: Apoorv Chaturvedi

email: support@turbodatatool.com;support@mndatasolutions.com

Phone:+91-8802466356

Increase productivity by more than 1000 percent for your Tally analysis

Increase Your ERP Productivity by Over 1000%

Many Tally customers experience bottlenecks that hinder their productivity due to several limitations in report generation. These issues often include slow processing times and difficulty in consolidating data. Below are some common challenges faced by users:

Common Issues with Tally:

1. Excel Export Limitations:
   • Exports are limited to data from a single company.
   • The exported data is not pivot-friendly, which complicates analysis and consolidation across multiple companies.
   • Manual repetition is required to export reports, as Tally reports do not refresh in real-time.
2. Inefficient Reporting for Ledgers and Stock Items:
   • In Tally, to access complete data for all ledgers and stock items, you must specify a single ledger and stock item entry. For instance, if you have 1000 ledgers and 500 stock items, generating reports requires 1000 x 500 iterations per day, significantly slowing down the process.

How Turbodata Addresses These Challenges:

Turbodata offers a solution that enhances the speed, accuracy, and efficiency of report generation, allowing businesses to achieve faster, real-time reporting across unlimited companies.

Here’s how Turbodata optimizes the process:

1. Extracts Data from Unlimited Companies:

   • We have streamlined the process to extract data across unlimited companies, enabling faster aggregation and analysis.

2. Utilizes JSON for Lightweight Extraction:

   • By using JSON for data extraction, we reduce the load time and increase the efficiency of data transfers.

3. Business Logic Transformation for Faster Processing:

   • We have converted Tally’s business logic into Power BI DAX and SQL, accelerating report processing. This eliminates the need for repeatedly accessing parameterized Tally reports with variables like ledger name and stock item name.

4. Optimized DAX Code for Speed:

   • Our DAX code is designed to speed up processing for large datasets, allowing you to analyze unlimited items and various ledgers without delay.

5. Group and Ledger Hierarchy Flattening:

   • We’ve flattened group and ledger hierarchies to improve the speed of report extraction, eliminating unnecessary complexity in data retrieval.

6. SQL Server Data Warehouse for Historical Data:

   • We use an SQL Server data warehouse to store historical data in 3rd normal form, supporting incremental data loads for continuous and faster reporting.

---

For Ultra-Fast Reporting and Analytics, Contact Us:

• Name: Apoorv Chaturvedi
• Email: support@turbodatatool.com | support@mndatasolutions.com
• Phone: +91-8802466356
• Youtube channel:youtube.com\@mndatasolutions2042

Boost your ERP productivity with Turbodata and experience seamless, real-time reporting!

---

This version is structured for clarity, and emphasizes the unique advantages of Turbodata over Tally.

Why Turbodata-ERP over Tally

Turbodata ERP vs Tally: Addressing Common Limitations

Turbodata ERP aims to address several common challenges faced by businesses using Tally, especially when dealing with large datasets, complex reporting, and scalability concerns. Here's how Turbodata ERP improves upon these issues and why it might be a better fit for certain businesses:

1. Handling Large Datasets

• Tally Limitation: Tally experiences slow speeds when dealing with very large data volumes. While Tally Server can partly alleviate this, the performance still degrades with huge amounts of transactional data.
• Turbodata Advantage: Built on SQL Server and Python, Turbodata ERP can handle large datasets much more efficiently. Relational databases, like SQL Server, offer faster querying, better indexing, and optimized data management, which ensures speed and responsiveness even with large datasets.

2. Data Integrity Post Company Split

• Tally Limitation: After splitting a company within Tally, issues such as incomplete bill receivables or distorted inventory valuations can arise.
• Turbodata Advantage: Turbodata ERP, powered by relational databases, can handle company splits without affecting data integrity. It ensures that financial data, inventory, and other business operations stay accurate and consistent, even after changes.

3. Multiple Companies and Branches

• Tally Limitation: Tally's architecture struggles when managing multiple companies across different states or branches, as each company might need separate databases.
• Turbodata Advantage: Turbodata ERP allows for centralized data management, enabling businesses to manage multiple companies across branches and states without the need for splitting data. The relational database design ensures scalability and consolidation of data.

4. Analysis of Ledger and Stock Vouchers

• Tally Limitation: Tally’s built-in reports are limited in scope and offer analysis ledger-wise and item-wise. For businesses that need more extensive analysis across multiple ledgers or stock items, it can be cumbersome.
• Turbodata Advantage: Turbodata ERP provides unlimited ledger and stock item analysis, which allows for more detailed and flexible insights across the entire data set. This is critical for businesses that require comprehensive reporting for better decision-making.

5. Data Security

• Tally Limitation: Tally uses a hierarchical database, which offers limited security, especially across large datasets with complex user roles.
• Turbodata Advantage: Turbodata ERP leverages the relational database model, offering much stronger data security and better control over access permissions. SQL Server supports role-based access control, making it easier to implement secure systems for managing sensitive data.

6. Speed of Reporting

• Tally Limitation: Due to the limitations of hierarchical databases, Tally can struggle with generating reports in systems that require many-to-many relationships. The reports are often slower when complex data relationships need to be analyzed.
• Turbodata Advantage: As Turbodata uses relational databases, it can handle many-to-many relationships much faster, allowing for faster reporting and more efficient data retrieval.

7. Tally XML Export

• Tally Limitation: Tally’s XML export API supports only one company at a time, which makes it difficult for users managing multiple companies.
• Turbodata Advantage: Turbodata ERP can handle data export for multiple companies simultaneously and integrates seamlessly with other systems. The XML integration and ODBC support also improve flexibility, making it easier to pull data from Tally.

8. API Limitations

• Tally Limitation: Tally uses an XML API that can be restrictive, especially for businesses requiring JSON-based integration or more modern integration methods with other software.
• Turbodata Advantage: Turbodata ERP supports JSON APIs, allowing for better compatibility with modern systems and ensuring easier integration with web applications, mobile apps, and other enterprise software.

9. Web and Mobile Access

• Tally Limitation: Tally-based web and mobile apps are only functional when Tally is open and running, limiting remote access.
• Turbodata Advantage: With Turbodata ERP, web and mobile apps are fully functional without needing to keep the desktop application open. This offers greater flexibility for users who require remote access to business data.

Conclusion: Why Choose Turbodata ERP Over Tally?

Turbodata ERP offers solutions to common issues faced by Tally users, especially when businesses need better performance, more comprehensive data analysis, scalability, and advanced integrations. If your business needs:

• Faster reporting with large datasets
• Comprehensive ledger and stock analysis
• Scalability for multiple companies and locations
• Better data security and control over operations

Then Turbodata ERP might be the right choice for you. It’s designed for businesses that require more sophisticated features, while Tally continues to be a solid choice for small to medium-sized enterprises focused on basic accounting and financial management.

For more information or inquiries about Turbodata ERP or integration with Tally, feel free to visit their official website or contact their support team.

Mndatasolutions youtube site:https://www.youtube.com/@mndatasolutions2042

email: support@turbodatatool.com;support@mndatasolutions.com

Phone:+91-8802466356

In a nutshell Turbodata is a datawarehouse developed in 3rd normal form using SQL Server directly integrated with Tally. The datawarehouse has now been developed into an ERP for data capture, data reporting and data analysis.

Standardized inventory valuation-Turbodata

 Turbodata seeks to offer a standardized inventory valuation methodology for the end clients. For example within Tally the inventory valuation has multiple options.

For example in the report stockvouchers the extract logic for the same is as follows[the stockvouchers report is aggregated to the stockitem monthly report ].The closing stock valuations are then used for profit and loss and balance sheet for the end client:

<ENVELOPE>

  <HEADER>

    <VERSION>1</VERSION>

    <TALLYREQUEST>Export</TALLYREQUEST>

    <TYPE>Data</TYPE>

    <ID>stockvouchers</ID>

  </HEADER>

<BODY>

<DESC>

<STATICVARIABLES>

        <EXPLODEFLAG>Yes</EXPLODEFLAG>

            <SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>

<stockitemname>angularstopcock</stockitemname>

                  </STATICVARIABLES>

      <TDL>

        <TDLMESSAGE>

<REPORT NAME="stockvouchers" ISMODIFY="Yes">

  <ADD>Set : SV From Date:"20240401"</ADD> 

  <ADD>Set : SV To Date :"20250430" </ADD> 

                                       

  <ADD>Set : ExplodeFlag : Yes</ADD>

                                                <ADD>Set : Onlyaccvoucher : Yes</ADD>

                </REPORT>

        </TDLMESSAGE>

      </TDL>

</DESC>

</BODY>

</ENVELOPE>

The report stockvouchers has multiple options as part of the variables. These variables values can be set to get different valuations for the end clients 

[Report: Stock Vouchers]

Use : Range Template

Use : Browser Common SysFormulae

    Title       : If (##IsItemBatchReport) Then $$SPrintf:@@GdwnVoucherTitleFormat:@@BatchTitle Else If ##IsItemBatchGdwnReport Then $$LocaleString:"Batch Godown Vouchers" Else $$LocaleString:"Stock Item Vouchers"

    Family      : $$LocaleString:"Stock Voucher Details"

    List Name   : $$LocaleString:"Voucher Details"

Help : @@HlpStock_Vouchers

Form        : Stock Vouchers

    Variables   : SV CurrentCompany, SV FromDate, SV ToDate, SV SortMethod

    Variable    : SVValuationMethod, SVCurrency, SVExchange, SVBudget, SVVariance

    Variables   : StockItemName, ExplodeFlag, ExplodeNarrFlag, IsDayBook, IsLedgerReport, IsItemReport, IsCCReport, IsGrpReport

    Variable    : DSPShowMonthly, DSPHasMultiLevel

    Variable    : OnlyInVouchers, OnlyOutVouchers

    Variable    : OnlyAccVouchers, OnlyInvVouchers

    Variable    : IsStockReport, IsGodownReport, IsMatConSum

    Variable    : DSPNotScaleable, DSPHasColumnTotal

    Variable    : IsMultiPage, InNewPages

    Variable    : VoucherTypeName, SVStockItem

    Variable    : DSPGodownName, DSPShowOpBal

    Variable    : DSPShowClosing, DSPShowInwards, DSPShowOutwards, DSPShowAdditionalDescription, DSPQtyAlternate

    Variable    : DSPShowQty, DSPShowRate, DSPShowValue, StkvchCostExplodeFlag

Variable : OnFocusInactiveDelete, IsItemBatchReport, DSPBatchName, IsItemBatchGdwnReport

; ;;Start Fresh page for each balancing method

Variable : BMInNewPage

Variables : LedStarting, LedNameStartingFrom, LedNameStartingTo

Variable : SVStockBalType

Variable : IsStockFromParty, IsPrintStockItem

  Variable : ShowGrossValue, ShowGPOnGV, ShowConsumption, DSPInvNameStyle

Variable : VchTotalTrans, ShowBasisOfValues, ExtractMode

  

Set : ShowBasisOfValues : Yes

    Set         : BMInNewPage : No

    Set         : SV SortMethod     : @@Default

    Set         : Is Item Report    : Yes

    Set         : Is StockReport    : Yes

    Set         : DSPShowMonthly    : No

    Set         : DSPHasMultiLevel  : No

    Set         : DSPNotScaleable   : Yes

    Set         : DSPHasColumnTotal : No

    Set         : IsMultiPage       : No

    Set         : InNewPages        : Yes

    Set         : OnlyInVouchers    : No

    Set         : OnlyOutVouchers   : No

    Set         : VoucherTypeName   : ""

    Set         : SVValuationMethod : ""

    Set         : SVCurrency        : ""

    Set         : SVExchange        : 1

    Set         : DSPShowInwards    : ##STKVCHInwards

    Set         : DSPShowOutwards   : ##STKVCHOutwards

    Set         : DSPShowClosing    : ##STKVCHClosing

    Set         : DSPShowQty        : ##STKVCHQuantity

    Set         : DSPShowRate       : ##STKVCHRate

    Set         : DSPShowValue      : ##STKVCHValue

Set : OnFocusInactiveDelete : No

Set : DSPBatchName : ##DSPBatchName

Set : IsItemBatchReport : ##IsItemBatchReport

Set         : IsItemBatchGdwnReport : ##IsItemBatchGdwnReport

Set        : DSPShowOpBal      : ##DSPShowOpBal

Set : StockItemName : #StockItemName

Set : IsPrintStockItem : No

    

These are very large number oif variables that one can set for the given report. The variations in the variables can result in differing numbers across companies, stcok items and even godowns. 

Problem statement we seek to solve:

a.) Give consistant numbers across all the stockitems for a company. In the current scenario one needs to choose the stock item to get the number. It is very difficult for the end client to go and check the valuations item by item. 

b.) No need to check the valuations and reports item by item. A consolidated standardized set of reports is there for the entire company to know its inventory valuations.

c.) Option of using weighted average or batch rate  whenever the client desires the same. One can maintain both batchwise and non batchwise inventory.

Problem resolution: Turbodata proposes to resolev this problem by building the inventory valuations from the base using DAX. For example the inward movements can be defined by extensive row and filter level contexts as follows:

INWARD_BALANCE_QTY =

/*ROW LEVEL CONTEXTS IN TERMS OF ITEM, COMPANY AND DATES ARE DEFINED HERE*/

VAR COMPANY_NAME='Stockitem_view'[Companyname]

VAR ITEM_NAME='Stockitem_view'[Itemname]

VAR DATE_COMPARE='Stockitem_view'[DocumentDate]

VAR B=

/*FIND THE RUNNING  SUM QTY OF THOSE VOUCHER INVENTORY ENTRIES WHERE VOPUCHER TYPE IS NOT SALES ORDER, PURCHASE ORDER, DELIVERY NOTE AND RECEIPT NOTE*/

CALCULATE(SUM('Stockitem_view'[Debit_quantity]),FILTER(all('Stockitem_view'),'Stockitem_view'[Itemname]=ITEM_NAME && 'Stockitem_view'[Companyname]=COMPANY_NAME  && 'Stockitem_view'[DocumentDate]<=DATE_COMPARE && Stockitem_view[Iscancelled_voucherinventory]="No" && Stockitem_view[Isvoid_voucherinventory]="No" && Stockitem_view[Isinvoice_voucherinventory]="yes" && NOT(Stockitem_view[VOUCHERTYPE_PARENT] IN {"SALES ORDER","PURCHASE ORDER","DELIVERY NOTE", "RECEIPT NOTE"})))+

/* FIND THE OPENING STOCK VALUE OF THE ITEM NON BATCH WISE. Add the same to the running sum */

calculate(max('Item Details'[OpeningBalance]),filter(ALL('Item Details'),'Item Details'[CompanyName]=COMPANY_NAME && 'Item Details'[Name.1]=ITEM_NAME))+

/* find the RUNNING SUM FOR THOSE DELIVERY NOTE ENTRIES WHERE THE TRACKING NUMBER ASSOCIATED WITH THE DELIVERY NOTE NUMBER IS NOT AVAILABLE IN THE SALES INVOICE*/

/*CALCULATE(SUM(Sale_missingtrackingnumber[Debitqty]),FILTER(Sale_missingtrackingnumber,Sale_missingtrackingnumber[STOCKITEMNAME]=ITEM_NAME && COMPANY_NAME=Sale_missingtrackingnumber[Company_name] && DATE_COMPARE>=Sale_missingtrackingnumber[DOCUMENTDATE]))

+*/

/* find the RUNNING SUM FOR THOSE RECEIPT  NOTE ENTRIES WHERE THE TRACKING NUMBER ASSOCIATED WITH THE RECEIPT NOTE NUMBER IS NOT AVAILABLE IN THE PURCHASE  INVOICE*/

CALCULATE(SUM(Purchase_missingtrackingnumber[Debitqty]),FILTER(ALL(Purchase_missingtrackingnumber),Purchase_missingtrackingnumber[STOCKITEMNAME]=ITEM_NAME && COMPANY_NAME=Purchase_missingtrackingnumber[Company_name] && DATE_COMPARE>=Purchase_missingtrackingnumber[DOCUMENTDATE]))

RETURN B

Adding the direct expenses associated with the inventory movement.

For the same the sample DAX is as follows:

a.) The value of Purchase accounts for the associated vouchers is calculated.

b.) tHE DIFFERENCE IS CALCULATED between the valaue calculated in a.) with the total inventory value at the item movement level.

c.) The amoount in b.) is then allocated to each item in proportion of the item amounts in inventory movement

d.) The value in c.) is then divided by the total quantity of the item in inventory movement to give the added component for direct costing.

WA_RATE =

VAR A='Stockitem_view'[INWARD_BALANCE_VALUE]/'Stockitem_view'[INWARD_BALANCE_QTY]

Var stockitemname=stockitem_view[itemname]

 VAR MASTERID=Stockitem_view[Masterid]

Var Total_debit_amount=Calculate(sum(Stockitem_view[Debit_Amount]),filter(all(stockitem_view),masterid=stockitem_view[masterid]))

 

Var total_debit_amount_ledger=Calculate(sum(Ledger_view[SaleAmounDebit]),filter(all(ledger_view),masterid=ledger_view[masterid]  && ledger_view[ledgerprimary]="Purchase accounts"))

 

Var difference=total_debit_amount_ledger-Total_debit_amount

 

Var total_amount_item=Calculate(sum(Stockitem_view[Debit_amount]),filter(all(stockitem_view),masterid=stockitem_view[masterid] && stockitemname=stockitem_view[itemname]))

Var total_quantity_item=Calculate(sum(Stockitem_view[Debit_quantity]),filter(all(stockitem_view),masterid=stockitem_view[masterid] && stockitemname=stockitem_view[itemname]))

Var directexpense_rate=difference*(total_amount_item/Total_debit_amount)

Var wa_rate_directexpense=directexpense_rate/total_quantity_item

RETURN a+wa_rate_directexpense

Here we are calculating the standard weighted average rate is cumulative inwardamount/cumulative inward quantity.

In order to add the direct expenses associated with the coucher, we have taken the following steps:

a.)

The code in the variable 'B' entailing missing trackling numbers has been incorporated in case

the onlyaccvouchers variable is set to No.

Based on the above Powerbi module the client can get a set documented inventory costing, weighted average methods across all items for all periods in time.

Shift to Turbodata sql server for better analysis

contact: Apoorv Chaturvedi

Phone:+91-8802466356

email:support@turbodatatool.com;support@mndatasolutions.com

youtube channel: https://www.youtube.com/@mndatasolutions2042

Indiamart site:https://www.indiamart.com/mnbusinessintelligenceindia/

Tally automation-automating the ledger vouchers and sales reports

 

Giving the monthly summary of the ledger Vouchers report for the sales entries.

 

Contact us for Tally spreadsheets automation

Name: Apoorv

Email: support@turbodatatool.com;support@mndatasolutions.com

Phone:+91-8802466356

 

Marketplace: https://www.indiamart.com/mnbusinessintelligenceindia/

 

 

The end client requires ledger vouchers view for all the partyledgernames associated with the sales entries.

That is the client is requiring a summary of ledger vouchers for each of the rolling months. 1-aug-2024 to 30-Aug-2024

 

The client format is as follows

 

We first handle the sales row.

The Tally api sales register extract is as follows:

<ENVELOPE>

<HEADER>

<VERSION>1</VERSION>

<TALLYREQUEST>Export</TALLYREQUEST>

<TYPE>Data</TYPE>

<ID>Sales Register</ID>

</HEADER>

<BODY>

<DESC>

<STATICVARIABLES>

<EXPLODEFLAG>Yes</EXPLODEFLAG>

<SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>

</STATICVARIABLES>

</DESC>

</BODY>

</ENVELOPE>

The sample output is as follows:

 

<ENVELOPE>

 <DSPPERIOD>April</DSPPERIOD>

 <DSPACCINFO>

  <DSPDRAMT>

   <DSPDRAMTA></DSPDRAMTA>

</DSPDRAMT>

  <DSPCRAMT>

   <DSPCRAMTA></DSPCRAMTA>

</DSPCRAMT>

  <DSPCLAMT>

   <DSPCLAMTA></DSPCLAMTA>

</DSPCLAMT>

</DSPACCINFO>

Limitations: 

·         Need python coding to set the svfromdate and svtodate(to one year)

·         Lack of flexibility in the Tally api output

·         Single company only

Implementation in Power BI through json api:

a.)    Unlimited companies

b.)    Flexibility in time granularity if required.

Column added for sales in DAX(sundry debtors debit amount):

debit_amount_debtors = 

var masterid1 = 

    SELECTCOLUMNS(

        FILTER(

            Ledger_view,

            Ledger_view[IsoptionalLedger] = "No" &&

            Ledger_view[Iscancelled] = "No" &&

            Ledger_view[Isvoid] = "No" &&

            Ledger_view[isactive_vouchertype] = "Yes" &&

            Ledger_view[Affects Stock new] = "No" &&

            Ledger_view[LedgerPrimary] = "Sales Accounts"

        ),

        "MasterId", Ledger_view[MasterId]

    )

var masterid2= Ledger_view[MasterId]

 

    

 

    

    var c= CALCULATE(SUM(Ledger_view[SaleAmounDebit])-SUM(Ledger_view[SaleAmounCredit]),FILTER(ALL(Ledger_view), Ledger_view[LedgerPrimary] = "Sundry Debtors" &&

    Ledger_view[MasterId]=masterid2 

    /*&& Ledger_view[IsoptionalLedger] = "No" &&

            Ledger_view[Iscancelled] = "No" &&

            Ledger_view[Isvoid] = "No" &&

            Ledger_view[isactive_vouchertype] = "Yes" &&

            Ledger_view[Affects Stock new] = "No" &&

            Ledger_view[LedgerPrimary] = "Sales Accounts" */

            && Ledger_view[MasterId] in masterid1))

 

return c

 

For the collections we need to calculate the credit amount for all the partyledgernames associated with the given sales entry.

 

Solution with Tally api:

 

<ENVELOPE>

                <HEADER>

                                <VERSION>1</VERSION>

                                <TALLYREQUEST>Export</TALLYREQUEST>

                                <TYPE>Data</TYPE>

                                <ID>Ledgervouchers</ID>

                </HEADER>

                <BODY>

                                <DESC>

                                                <STATICVARIABLES>

                                                                <EXPLODEFLAG>Yes</EXPLODEFLAG>

                                                                <SVEXPORTFORMAT>$$SysName:XML</SVEXPORTFORMAT>

<ledgername>AAFIYA LEATHER REX</ledgername>

                                </STATICVARIABLES>

                                                <TDL>

                                                                <TDLMESSAGE>

                                                                                <REPORT NAME="Ledgervouchers" ISMODIFY="Yes">

                                                                                                <ADD>Set : SV From Date:"20240401"</ADD>

                                                                                                <ADD>Set : SV To Date :"20250430" </ADD>

                                      

                                                                                                <ADD>Set : ExplodeFlag : Yes</ADD>

                                                                </REPORT>

                                                </TDLMESSAGE>

                                                </TDL>

                                </DESC>

                </BODY>

</ENVELOPE>

 

For each ledger entry one will have to get the debit amount and the credit amounts at ledger voucher level  on a  day by day basis.

<ENVELOPE>

 <DSPVCHDATE>25-Aug-24</DSPVCHDATE>

 <DSPVCHLEDACCOUNT>GC Works</DSPVCHLEDACCOUNT>

 <NAMEFIELD></NAMEFIELD>

 <INFOFIELD></INFOFIELD>

 <DSPVCHTYPE>Sale</DSPVCHTYPE>

 <DSPVCHDRAMT>-1606200.00</DSPVCHDRAMT>

 <DSPVCHCRAMT></DSPVCHCRAMT>

 <DSPVCHDATE>25-Aug-24</DSPVCHDATE>

 <DSPVCHLEDACCOUNT>GC Works</DSPVCHLEDACCOUNT>

 <NAMEFIELD></NAMEFIELD>

 <INFOFIELD></INFOFIELD>

 <DSPVCHTYPE>Sale</DSPVCHTYPE>

 <DSPVCHDRAMT>-2129484.00</DSPVCHDRAMT>

 <DSPVCHCRAMT></DSPVCHCRAMT>

</ENVELOPE>

 

Solution through JSON api:

Benefits:

a.)    Unlimited ledgers

b.)    Unlimited companies

c.)     Flexible time frame.

 

DAX CODE is as follows:

Credit_amount_debtor = 

var masterid1 = 

    DISTINCT(SELECTCOLUMNS(

        FILTER(

            Ledger_view,

            Ledger_view[IsoptionalLedger] = "No" &&

            Ledger_view[Iscancelled] = "No" &&

            Ledger_view[Isvoid] = "No" &&

            Ledger_view[isactive_vouchertype] = "Yes" &&

            Ledger_view[Affects Stock new] = "No" &&

            Ledger_view[LedgerPrimary] = "Sales Accounts"

        ),

        "MasterId", Ledger_view[MasterId]

    ))

    var masterid2=Ledger_view[MasterId]

var partyledgername=distinct(SELECTCOLUMNS(FILTER(ledger_view,ledger_view[masterid] in (masterid1)),"partyledgername",ledger_view[partyledgername_sales]))

Var creditor_amount=calculate(sum(Ledger_view[SaleAmounCredit]),filter(all(ledger_view),

Ledger_view[Name] in partyledgername && Ledger_view[MasterId] = masterid2))

 

RETURN creditor_amount