Hi,
I have a report where I am exporting the Posting Date but want to export it in a MM/YYYY format. I do not mean just changing to format in Excel which is easy.
From the Item Analysis table I an exporting the Posting Date, Location Code, Sales Amount (Actual). but want to group this by month/year not day.
Can anyone help with this please.
Hi Stuart -
I am assuming you are working in Dynamics NAV (using the Item Analysis View Entry table?)
You mention that you have a report.
I assume it looks something similar to this:
How are you "exporting" the data that is in the report?
Hi Harry,
Yes we are using Dynamics Nav 2017 and you are correct with the table also.
This is just a report and you are also correct with your data this is what I am getting. I do know that its pulling the Posting Date and Excel is just formatting the Date as MM/YYYY. What I want is to convert the date format before it gets to excel so I can group by month.
Sorry for the lack of information in my first post and hope this makes more sense.
Thanks
Stu
Hi Stuart.
Here is how I would handle that. I would create a Grouping Report.
For my grouping, I would return the Posting Date field (filtered by whatever date range I need) with a format applied:
=NL("Rows=2","Item Ledger entry","=TEXT(NF(,""Posting Date""),""mm/YYYY"")","Posting Date","10/1/20..12/31/20")
(in this example, that would return: 10/2020 11/2020 12/2020 )
Then, my subordinate function would use the NP(Datefilter) function to build the date range needed to filter its Posting Date:
=NL("Rows","Item Ledger entry",,"Posting Date",NP("Datefilter",C4,EOMONTH(C4,0)))
Here is what my sample looks like:
(you should be able to right-click that and open a larger view)
When it runs, it looks something like this:
Thanks Harry,
That would work but what I am wanting to do is not show the posting date but the MM/YYYY and a SUM of Sales by Location which would look something like this.
| Oct-20 | ALT-WHSE2 | 789 | ||
| Nov-20 | ALT-WHSE2 | 982 | ||
| Dec-20 | ALT-WHSE2 | 856 | ||
Sorry more like this
Hi Stuart.
Oh! you want to just show totals, not individual transactions?
Here is an example showing totals by month and location:
Cell B4: =C4
Cell B5: =B4
Cell C4: =NL("Rows=2","Item Ledger entry","=TEXT(NF(,""Posting Date""),""mm/YYYY"")","Posting Date","10/1/20..12/31/20")
Cell D5: =NL("Rows","Item Ledger Entry","Location Code")
Cell D6: =NL("Sum","Item Ledger entry","Quantity","Posting Date",NP("Datefilter",B5,EOMONTH(B5,0)),"Location Code",D5)
When run:
Hi Harry,
That is perfect, thank you so much for your help.
Cheers
Stu