Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Small and medium business | Business Central, N... / Sales Analysis Views i...
Small and medium business | Business Central, N...
Suggested answer

Sales Analysis Views in Excel

(1) ShareShare
ReportReport
Posted on by DG-09071510-0 26
Hi all,
 
I saw this video and was inspired to attempt this with our BC.  https://www.youtube.com/watch?v=IoHyv3_eu2g
 
I have setup the sales analysis by compressing the date by month, starting on 01 Jan last year.
 
I can view the matrix by date range over the entire previous year and by listing quantity by period and viewing per month.  The view in BC is as expected.
 
But, when I export to Excel I have columns for day, week, month, quarter, and year.  I had assumed by compressing dates by month, and viewing by month, that would be how the excel data would display.
 
My problem is that I have some items with more than one entry per month.  In the video above, The gentleman uses the exported data to import back into BC as a forecast for next year.  There is a break in his video where he likely reworks the data to have 12 rows per item, which is not shown.
 
Is it possible to export this sales analysis in a monthly view, that is not more than 12 rows per item?  As you can see the exported excel file below, item 20002 has 15 rows.  I'm in Europe and the dates are day.month.year
 
Thanks for your thoughts!
 
Categories:
Dynamics 365 Business Central
Dynamics 365 Business Central
Dynamics 365 Business Central
  • DG-09071510-0 Profile Picture
    DG-09071510-0 26 on at
    Sales Analysis Views in Excel
    Thanks for your input Kaspars
     
    I've exported the table 7154 to excel and compared the same item number 20002 from above.
     
    In table 7154, the only difference I see is that there are some additional entries from 2022 and 2024, while the sales analysis view export was filtered to 2023 sales only.  Otherwiese the two excel tables match.
     
    I noticed, while scrolling through the item card postings, that in August there were two sales orders for the same customer and same delivery date.  I suspect these were invoiced together.  these two sales quantities 40 and 20 pieces are combined in the sales analysis view as 60 pieces in August.  But there is a second entry for 30 in August which was another customer at a later date. 
     
    The sales analysis view does combine all sales by month in the matrix view within BC. if I click on the August 90 number, I see a list of two postings 60 and 30.
     
    Unfortunately "related > actions > export to excel" does not keep the monthly consolidation but shows both postings.
    If I export to excel using the share button, I get the monthly consolidation, but the table format is the same as the sales view matrix with columns for each month.
     
    Ultimately I want to get the format with the item and month as 12 or less rows, in hopes of importing this somehow back into BC as a forecast.
     
    Side Question: could I import as a forecast in its existing state with multiple quantities at the same date?  Continuing from above, August 01 has two entries 60 and 30.  Would the forecast import recognise both quantities?  I've found table 99000852 Demand Forecast Entry, which I can export and view forecast entries.  But how can I import from excel back to BC?
     
    Many thanks!
  • Suggested answer
    KasparsSemjonovs Profile Picture
    KasparsSemjonovs 3,960 Super User 2024 Season 2 on at
    Sales Analysis Views in Excel
    You would need to understand why some items have more than 12 entries. Check the "Sales Analysis Views".
    Afterwards You can try to export via Configuration package the data from table 7154 - Item Analysis View Entry, and filter out for specific analysis view/item no. and check if there are any differences. 
    If Dimensions are included in the Analysis view, it could be both entries have some different Dimension value, and that is why there is more than 1 entry in the month. 
    Or Item was posted to different Location Code.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Congratulations 2024 Spotlight Honorees!

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December!

Congratulations to our December super stars! 🥳

Get Started Blogging in the Community

Hosted or syndicated blogging is available! ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,602 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,340 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans