Hi
I have created a report for a client who wants to select a date range which may cover several fiscal years.
This report extracts from the NAV Value Entry table.
Its lists sales amounts per item per customer (grouped by customer)
However, if there are several years selected in the date range, the report detail, should group by the year (not detailed posting date) of the transactions, filtered by the posting date filter in the option. I have tried excel functions together with Jet functions such as YEAR(NL(Rows=2,Value Entry,Posting Date,Posting Date,$C$5), but I still dont get a result to be able to group the report details by.
It should also automatically get a YTD for each year's rows
Attached is an exampleCustomer Sales Example.xlsx
Date
Votes
2 comments
-
Jet Reports Historic Posts Official comment Hi
Thanks very much for your post. Although I didnt use it exactly as per your suggested solution, it did help me an enormous amount to get a function and get what I wanted. I never knew one could use something like this :=YEAR(NF(,""Posting Date""))". Well, experience is wonderful, isnt it?
The function / formula =NL("Rows=2","Value Entry","=YEAR(NF(,""Posting Date""))","Posting Date",$C$5), seems to use a lot of report runtime, however, I did use the following variation of it in another Jet function
=NL("Rows=7","Accounting Period","Starting Date","New Fiscal Year",TRUE,"=YEAR(NF(,""Starting Date""))",">="&YEAR(NL("First","Value Entry","Posting Date","Posting Date",$C$5,"Source Type","Customer","Item Ledger Entry Type","Sale")),"Starting Date",$C$5).
Maybe you can shed light on a new post that I am making, re grouping by shortcut dimensions - a real challenge, since not all entries use them and the ledger entry dimension table in NAV ONLY shows entries with dimension values.
H -
Jet Reports Historic Posts I think you could do this with a calculated field like this:
=NL("Rows=2","Value Entry","=YEAR(NF(,""Posting Date""))","Posting Date",$C$5)
Of course, then you would have to also use a calculated filter to replicate out value entries for that year. Something like this might work (assuming my previous formula was in D10):
=NL("Rows","Value Entry",,"=YEAR(NF(,""Posting Date""))",D10,"Posting Date",$C$5)
Does that work for you?
Regards,
Hughes
Please sign in to leave a comment.