Forum Replies Created
-
Posts
-
In the SQL Server code, try:
AND tblsale.DATE > '2015-01-31'From the syntax of your SELECT statement, it looks like your SQL database is running MS Access. Since MS Access is not as robust as SQL Server, running queries with multiple tables could slow your system. Probably getting your data in chunks will be better for you.
You could try this query which attempts to set the date filter using the DateValue() function:
SELECT tblsale.sale_link, tblsale.loc_code AS [LOC#], tblsale.DATE AS [TRANS DATE], tblsale.TIME, tblsale.trans_no AS [TRANS#], tblsale.customer_code AS [CUST#], tblsalecommission.salesperson, tblsaleitem.sku_no AS [SKU#], tblsaleitem.description, Min(tblsalediscount.disc_code) AS [DISCOUNT CODE], tblsaleitem.qty AS [UNITS SOLD], tblsaleitem.ext_cost AS COST, [unit_ext_price] – [unit_ext_best_price] AS DISCOUNT, tblsaleitem.unit_ext_price, tblsaleitem.unit_ext_best_price AS [NET RETAIL], tblsaleitem.line_no, tblsale.trans_type, tblsale.void_code, tblsale.suspended, tblsaleitem.affect_inv, tblsaleitem.affect_total, tblsale.note AS [SALE NOTE], tblsaleitem.note AS [SALE ITEM NOTE] FROM tblsalediscount RIGHT JOIN (tblsale INNER JOIN (tblsaleitem INNER JOIN tblsalecommission ON ( tblsaleitem.line_no = tblsalecommission.line_no ) AND ( tblsaleitem.sale_link = tblsalecommission.sale_link )) ON tblsale.sale_link = tblsaleitem.sale_link AND tblsale.DATE > DateValue ( "1/31/2015" ) ON ( tblsalediscount.line_no = tblsaleitem.line_no ) AND ( tblsalediscount.sale_link = tblsaleitem.sale_link ) GROUP BY tblsale.sale_link, tblsale.loc_code, tblsale.DATE, tblsale.TIME, tblsale.trans_no, tblsale.customer_code, tblsalecommission.salesperson, tblsaleitem.sku_no, tblsaleitem.description, tblsaleitem.qty, tblsaleitem.ext_cost, [unit_ext_price] – [unit_ext_best_price], tblsaleitem.unit_ext_price, tblsaleitem.unit_ext_best_price, tblsaleitem.line_no, tblsale.trans_type, tblsale.void_code, tblsale.suspended, tblsaleitem.affect_inv, tblsaleitem.affect_total, tblsale.note, tblsaleitem.note;`
Steven,
Interesting. There is a lot going on in your 15 or so tables.
When I was responsible for inventory information, my life was made (relatively) simple because the SQL database had a single table that recorded all inventory movements including transfers, adjustments, receipts, and sales for all SKUs.
If you are importing your data from SQL Server, or other relational database that supports views, you (or your IT Department) could probably append transfers, adjustments, receipts and sales into an equivalent inventory movement table and possibly eliminate all of your week-by-week calculations with a handful of formulas that could work with pivot filter contexts.
Alberto Ferrari and Marco Russo also have some insights into inventory tables, notably in their “Definitive Guide to DAX” on pages 178 – 188 which you would probably find helpful.
Also, instead of using the TODAY(), I like to use a data freshness date based on something like the calendar’s Last_Refreshed column or the Trans Journal’s posting date. The problem I find with TODAY() is that it introduces a change when the underlying data is static, which leads to some people feeling that the same workbook data is giving them different versions of the truth.
Your data model also has many columns, perhaps not all of them are used. During data refresh, it is not just the import that adds to the total time, but also that the formula engine is optimizing each column and over millions of rows that can add minutes to the overall time.
Also, I may have asked before, but are you using Excel 2016 or Excel 2013 at work?
Tom
Steven,
I have downloaded file, you can disconnect dropbox link.
Tom
Dropbox is OK. Please make sure that the data is “sanitized” (does not reveal any sensitive company information).
You could also filter the contents down to a few parts/items that have a sufficiently long history of activity.
Best regards,
Tom
There has to be a simpler way. Could you put up some sample data and we could discuss?
I might be wrong how I see your data model, but right now I am picturing that you are trying to write a formula with just one table and, if so, I believe that will prove a pain point for this case.
Could you put together some sample data and post? The sample data should either be fictitious or anonymized, but your data model should be consistent with your production version.
Tom
If it were me, I would use Power Query to create a table of unique items and relate this new table to Transfers.
In the new Items table, I would create a calculated to determine the actual first date each product was sold. It would look something like this:
Item First Sales Date = MINX ( RELATEDTABLE ( Transfers ), [Journal Date] )
Additionally, I would create a calculated column in the same Items for the number of weeks from today for the first sales date and name that column something like “Weeks in Stores”
Then I would create a pivot with items on rows and create a slicer using the “Weeks in Stores” column.
At that point, I could add put any calendar unit on pivot columns or rows, and place any measure for transfers to stores inside the pivots drop zone, and based on slicer selection for “Weeks in Stores”, I could find out how items were performing.
Is the receiving table the same as TRANSFERS?
Another question: Assuming the Transfers table contains information about items received in stores, does it contain other records like information shipped to stores or items returned from stores?
Or is it possible to have an event where a non-item (non-product) is received at a store (for example, uniforms for employees, cleaning-supplies for store maintenance, etc.) and such transactions are recorded in the transfers table?
Firstly, to clarify for readers regarding “in a pivot table you can’t filter a measure”: Measures are filtered in a pivot table by content in rows, columns, filter and slicers (these four items often referred to as the “original” filter context).
Questions for you:
Do you want to know, for each store, which products were first received in the last 3-5 weeks or do you want to know which products were first received at any store in the last 3 to 5 weeks?
When you mention the “last 3 to 5 weeks” are you only interest in items first received between week 3 and week 5, OR something else?
What is the name of the column that contains the formula: “=if(TODAY()-[JOURNAL DATE]<7,1,if([JOURNAL DATE]=BLANK(),999,(TODAY()-[JOURNAL DATE])/7))"?
Is the calculated column referenced above in the Transfers table?
Does your data model have lookup tables for Items and Stores?
Shae,
First, all three of the books you have listed are definitely some of the best available. If you use Excel 2016 or Power BI, some of “M is for Data Monkey” is obsolete, namely the feature for importing multiple files from a single folder. However, Puls and Escobar are scheduled to release the next edition of the book with the title “Master Your Data with Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow” in November of this year (2018).
Looking at your data model, there is an issue with your bridge table because it uses a whole number data type for the sales order number, while the corresponding values in sales and purchase invoice tables are text. This will definitely affect your results when using the bridge table.
Since Power Query uses a source for the bridge table that I cannot access, I will create a modified solution using only the fact tables.
Initially, a calculated column (Pending Freight Order) will be added to the sales invoices table with the following formula:
= IF ( COUNTROWS ( FILTER ( 'Purchase_Invoices', 'Purchase_Invoices'[Freight Sales Order] = 'Sales_Invoices'[Sales Order No_] ) ) = 0, TRUE (), FALSE () )Then a measure can be created:
Estimated Freight For Pending Freight Orders := SUMX ( FILTER ( Sales_Invoices, Sales_Invoices[Pending Freight Order] ), Sales_Invoices[Estimated Freight] )Then a pivot can be created using just the new measure and columns from the sales invoice table (Carrier Code, Sales Invoice No_, Ship-to Country).
However, in the attached workbook, my pivot shows also shows a sales invoice number (sales order number) that was not found in the bridge table nor the purchase order table. I took this matter to be an issue with the bridge table (that it should have had the missing sales order number) instead of an issue with the measure. If you truly only want to use sales invoice records that have a matching record in the bridge table, let me know and I will make the corrections.
Tom
You’re welcome!
Tom
eisayev,
A tricky case! You have two pivots on one sheet, but the slicer is only controlling (connected to) one. Click in any cell of the larger pivot (pivottable2), then in the Pivot Table Tools context menu, under the Analyze tab, locate the Filter group and click on the Filter Connections button. When the Filter Connections dialog box opens, you will notice the checkbox to the left of “Values” is blank. After entering a check mark and choosing OK, your slicer will now control both pivots.
By default, slicers only affect the pivot where they were created. Controlling other pivots requires the steps above.
Slicer in attached workbook controls both pivots.
Tom
