Hi. I would like to be able to extract summed data from a table. What is the best way to achieve this?
eg I have a table (Item ledger Entries) which has multiple entries by Transaction (eg sales/purchases), and by Product.
How do I pull a Summary Table that sumarises (totals) the data for, say, two fields (Product & Transaction Type)?
Date
Votes
7 comments
-
CascadiaDataworks I'm not quite envisioning what you are trying to explain. Are you able to create a mockup of what you need? Would you need a row for product and a column for transaction types, and then totals for each product for each transaction type?
-
Richard Allen The table has millions of lines; I want to summarise.
This is an example – I want the output to have unique outputs for Item_No-Description-GL_Acc-Entry_Type with a sum of Qty & Cost£
i.e.
- there may be 100s of rows of negative adjustments for Pencils; I want to sum these.
- Likewise I want to sum all the table rows for Calculator_Purchases, separately for all the table rows for Calculator-Pos_Adjusts
-
CascadiaDataworks Ah, now I see. Review the support article on Field Cache. It does a terrible job of explaining but what a field cache will do is create a unique combination of the fields you provide, so in your case, it looks like the Field parameter of your NL(Rows) would look like {"Item No_","Item_Description","G_LAccount No","Entry Type"} and then whatever filters you need. Then you can use NFs to get the actual values for those fields and NL Sum to get your totals.
-
CascadiaDataworks It won't let me post a link to the KB article, just search Field Cache
-
Richard Allen I have not had much experience of tables; what type of table would work best?
Use Report Wizard? I ran the following report; but it returned every line in the table; not a sum.
-
CascadiaDataworks You will not be able to use the Report Wizard or Table Builder. You will need to use the NL functions to build this report, and you previously mentioned the Item Ledger Entry table, so you would be using that one.
If you need assistance, I offer up help. My username is also my company name and website.
-
CascadiaDataworks Checking in to see if you still would like some assistance. Happy to give you 15 minutes free to get you what you need, or at least get you started (shouldn't need more than 15 minutes if you just need your screenshot). And if you decide later you want more help or training, you will know where to find me.
Please sign in to leave a comment.