Skip to main content
Business Central forum
Suggested answer

Sales Analysis Views in Excel

editSubscribe (1) ShareShare
ReportReport
Posted on by 18
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:
  • Community member Profile Picture
    Community member 18 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 2,975 Super User 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.

Helpful resources

Quick Links

What Motivates a Super User?

We know many of you visit the Dynamics 365 Community and Power Platform…

Demystifying Copilot with Georg Glantschnig…

Industry experts answer burning questions directly from our amazing Community…

Setting Up Knowledge Sources for Copilot…

Look at how configuring a comprehensive knowledge base is crucial…

Leaderboard

#1
Andre Arnaud de Calavon Profile Picture

Andre Arnaud de Cal... 283,045 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 222,570 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,138

Product updates

Dynamics 365 release plans