Good day all,
I am trying to sum all the Sales Amount (Actual) entries (Dynamics NAV) in my item ledger entries for the month of January only but for all years.
In other words, whether its January 06, January 08 or January 2015, it must sum all those January entries in one cell.
I have 12 cells, one for each month so the 2nd cell will sum all February Sales Amount (Actual) etc.
Please can someone help if this is possible.
Many thanks
Tom
Date
Votes
2 comments
-
Jet Reports Historic Posts My brain says that you're going to have to build a rather complex date filter for this, something like:
01/01/2005..01/31/2005|01/01/2006..01/31/2006|01/01/2007..01/31/2007|and so on through to 2014
I think if it were me, I'd be more inclined to sum January of each year in a separate row or column, then bring those values over to your final page using a regular Excel SUMIFS formula -
Jet Reports Historic Posts the design of the report is in the Options tab.
1. Create an list of dates using the NP Dates function
2. Create an list of resulting dates using the Rows function using the date list at step One
3. Using the Edate function convert this into the start of the month required
4. Workout the end of each month
5. Using the NP Date Filter function create a list on months
6. Using the NP Join function convert this into an array using the Pipe Separator
7. Copy the result and remove the last |
You now has a filter that you can use for your posting dates
I know that this looks complicated but if you see the attached sample it is not so bad, the problem is in creating the date filter array
Enjoy
Malcolm
Please sign in to leave a comment.