Can someone please help me? I've been desperately trying to create a list of customer codes (source no.), in order by sales (amount) over a certain period of time from the item ledger history table. This is my first attempt at trying this new way of sorting by sum, and from reading the other posts, it looks like it should work, but it's not sorting them correctly. It still sorts them by source no. Any help would be greatly appreciated. See my formula below:
=nl("Rows","item ledger history","source no.","+=NL(""Sum"",""item ledger history"",""Amount"",""posting date"","""&$B$2&""",""entry type"",""sale"",""source no."",""CA-*"",""link="",""customer"",""no."",""=source no."",""salesperson code"",""VWM*"")","*","entry type","sale","source no.","CA-*","posting date",daterange,"link=","customer","no.","=source no.","salesperson code","VWM*")
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi,
You almost have it right. The only thing you're missing is the NF formula linking your embedded NL(Sum) formula with the outer NL(Rows) formula. Without this, Jet is always doing a sum of all the customers in the Item Ledger History table so the sort doesn't do anything since all the sums are the same. It would look something like this:=nl("Rows","item ledger history","source no.","+=NL(""Sum"",""item ledger history"",""Amount"",""Source No."",NF(,""Source No.""),""posting date"","""&$B$2&""",""entry type"",""sale"",""link="",""customer"",""no."",""=source no."",""salesperson code"",""VWM*"")","*","entry type","sale","source no.","CA-*","posting date",daterange,"link=","customer","no.","=source no.","salesperson code","VWM*")
You might actually be able to speed up this formula by replicating your customers from the Customer table and then linking to the Item Ledger History table since the Customer table is much smaller. Also, since each sum is only for 1 specific customer, there is no need for your Link= to the customer table in the NL(Sum) formula. I think it would look like this:=nl("Rows","Customer","no.","+=NL(""Sum"",""item ledger history"",""Amount"",""Source No."",NF(,""No.""),""posting date"","""&$B$2&""",""entry type"",""sale"")","*","salesperson code","VWM*","No.","CA-*","Link=","Item Ledger History","Source No.","=No.","entry type","sale","posting date",daterange)
Does that work for you?
Regards,
Hughes -
Jet Reports Historic Posts Hugh,
Thank you so much! You helped me to make my deadline today! I'm extremely grateful for that, and excited to be able to use this in more of my reports in the future.
I also took your advice and started with the customer table, and then linked to the item ledger history. That worked beautifully!
Thanks again!
Denise
Please sign in to leave a comment.