## Forum Replies Created

Viewing 15 posts - 1 through 15 (of 17 total)
• Author
Posts
• in reply to: Distinct Count #15508

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

I’ve attached a screenshot of the table.

###### Attachments:
You must be logged in to view attached files.
in reply to: First date in table #15507

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Thanks for the post.

That did the trick.

Can you explain the FIRSTNONBLANK LINE?

I have a pitvottable I created with the sales results. I have some transactions where an item was exchanged. So the value of the net transaction is zero. But it still counts as a transaction and therefore the sales associate is in my list. I would like to filter on transactions <>0.

But you can’t filter measures. IE a months worth of sales for you equal 0 at location 1. So I don’t need you listed. But in location 2 you have sales of \$100. So you should be listed.

How to I get the list to only list non zero NET transactions.

in reply to: First date in table #15488

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

It would be even better to see all the pay rates for each employee Ranked.

To see each date we provided you an increase.

thanks

in reply to: First date in table #15486

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Not sure if this helps display the issue.

###### Attachments:
You must be logged in to view attached files.
in reply to: Distinct Count problem #10408

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Sorry to ask another question. I’m also looking to get the same formula for 1 year ago. Here is my old formula:

Steven

in reply to: Distinct Count problem #10407

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Wow, thanks a lot. That totally did the trick.

I thought “SUMX” was to be avoided? Something about being slow over a large system?

Steven

Cheers

in reply to: Distinct Count problem #10403

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

See attached image that didn’t display in the post.

###### Attachments:
You must be logged in to view attached files.
in reply to: Help with weekly query of sales and inventory #9971

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Hi this is the error message i receive with the new statement:

DataSource.Error: Microsoft SQL: ‘DateValue’ is not a recognized built-in function name.
Details:
DataSourceKind=SQL
DataSourcePath=server;DB0001_160517195223
Message='DateValue' is not a recognized built-in function name.
Number=195
Class=15

in reply to: Help with weekly query of sales and inventory #9970

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Hi Tom I will try your suggestions on selecting dates when I’m at the office today.

As as for the Access statement I first write the query in access as I have More experience doing that. Then I take it to an online site and convert the MS access SQL statement to a sql server statement. Is there a better way of me doing it as I’m not familiar with writing SQL statements. I thought it was just converting it and would now bypass any connections are called to the access database

in reply to: Help with weekly query of sales and inventory #9967

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Hi Tom, another question. I want to limit the sales, transfers, receivings and so on to a start date of >1/31/2015

I can only seem to do it in POWER QUERY:

let
Source = Sql.Database(“SERVER”, “DB0001_160517195223”, [Query=”SELECT tblsale.sale_link,#(lf) tblsale.loc_code AS [LOC#],#(lf) tblsale.DATE AS [TRANS DATE],#(lf) tblsale.TIME,#(lf) tblsale.trans_no AS [TRANS#],#(lf) tblsale.customer_code AS [CUST#],#(lf) tblsalecommission.salesperson,#(lf) tblsaleitem.sku_no AS [SKU#],#(lf) tblsaleitem.description,#(lf) Min(tblsalediscount.disc_code) AS [DISCOUNT CODE],#(lf) tblsaleitem.qty AS [UNITS SOLD],#(lf) tblsaleitem.ext_cost AS COST,#(lf) [unit_ext_price] – [unit_ext_best_price] AS DISCOUNT,#(lf) tblsaleitem.unit_ext_price,#(lf) tblsaleitem.unit_ext_best_price AS [NET RETAIL],#(lf) tblsaleitem.line_no,#(lf) tblsale.trans_type,#(lf) tblsale.void_code,#(lf) tblsale.suspended,#(lf) tblsaleitem.affect_inv,#(lf) tblsaleitem.affect_total,#(lf) tblsale.note AS [SALE NOTE],#(lf) tblsaleitem.note AS [SALE ITEM NOTE]#(lf)FROM tblsalediscount#(lf) RIGHT JOIN (tblsale#(lf) INNER JOIN (tblsaleitem#(lf) INNER JOIN tblsalecommission#(lf) ON ( tblsaleitem.line_no =#(lf) tblsalecommission.line_no )#(lf) AND ( tblsaleitem.sale_link =#(lf)tblsalecommission.sale_link ))#(lf)ON tblsale.sale_link = tblsaleitem.sale_link)#(lf)ON ( tblsalediscount.line_no = tblsaleitem.line_no )#(lf)AND ( tblsalediscount.sale_link = tblsaleitem.sale_link )#(lf)GROUP BY tblsale.sale_link,#(lf) tblsale.loc_code,#(lf) tblsale.DATE,#(lf) tblsale.TIME,#(lf) tblsale.trans_no,#(lf) tblsale.customer_code,#(lf) tblsalecommission.salesperson,#(lf) tblsaleitem.sku_no,#(lf) tblsaleitem.description,#(lf) tblsaleitem.qty,#(lf) tblsaleitem.ext_cost,#(lf) [unit_ext_price] – [unit_ext_best_price],#(lf) tblsaleitem.unit_ext_price,#(lf) tblsaleitem.unit_ext_best_price,#(lf) tblsaleitem.line_no,#(lf) tblsale.trans_type,#(lf) tblsale.void_code,#(lf) tblsale.suspended,#(lf) tblsaleitem.affect_inv,#(lf) tblsaleitem.affect_total,#(lf) tblsale.note,#(lf) tblsaleitem.note; “]),
#”Filtered Rows” = Table.SelectRows(Source, each [TRANS DATE] > #datetime(2015, 1, 31, 0, 0, 0)),
#”Changed Type” = Table.TransformColumnTypes(#”Filtered Rows”,{{“TRANS DATE”, type date}, {“TIME”, type time}, {“UNITS SOLD”, Int64.Type}, {“COST”, Currency.Type}, {“DISCOUNT”, Currency.Type}, {“NET RETAIL”, Currency.Type}}),
#”Changed Type1″ = Table.TransformColumnTypes(#”Changed Type”,{{“unit_ext_price”, Currency.Type}}),
#”Filtered Rows2″ = Table.SelectRows(#”Changed Type1″, each ([void_code] = null) and ([affect_total] = true)),
#”Filtered Rows3″ = Table.SelectRows(#”Filtered Rows2″, each [suspended] = null),
#”Renamed Columns” = Table.RenameColumns(#”Filtered Rows3″,{{“unit_ext_price”, “RETAIL”}, {“description”, “DESCRIPTION”}})
in
#”Renamed Columns”

But this is the starting point from my SQL statement. Should I filter the dates here? And if so how? I keep getting error messages when I try. My statement must be wrong.
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 )
ON ( tblsalediscount.line_no = tblsaleitem.line_no )
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;

in reply to: Help with weekly query of sales and inventory #9966

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Hi Tom, unfortunately I am the IT department. The server SQL DB does have some “views” I think. But they are limited to CUSTOMER, SALES, PRODUCT and a few others nothing along the line of Inventory Movement.

I could try to build a SQL statement that pulls all the information and tables together. Not sure if that would be very slow. I thought I had read smaller chunks are more efficient.

Pulling so many tables together would it not slow down the system?

I would connect to the SERVER and filter SALES, RECEIVINGS, TRANSFERS and so on for maybe the last 3 years. Then use the OH as of today to roll back to prior dates?

Thanks for all your help and insights.

Steven

in reply to: Help with weekly query of sales and inventory #9955

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Good morning Tom, i’ve attached a link.

Just a warning I’m self taught. I do my best to read up, follow sites such as yours and allow life to continue to teach me.

I’m open to any thoughts on speeding things up as my database connects to our server using OBDC on a SQL. I try to limit the data i query to 3 years wherever possible. But for some processes it can take 20 minutes to poll.

https://www.dropbox.com/s/p1tzt84wuwenghm/HBC%20Sample.xlsx?dl=0

Thanks, Steven

in reply to: Help with weekly query of sales and inventory #9947

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

My xlsb file is 37mb. Not sure what I could do.

Is there another way to share it? Dropbox?

in reply to: Need to filter a calculated measure #9865

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

<span style=”color: #333333;”>For this case yes.

Items are received against the receiving table to the warehouse. And then transferred from the warehouse to each store.

So for analysis I only need to look at the transfer table and the column first sent to stores.

If he item has been sent many times I only look at MAX being the highest number under firsts sent to stores.</span>

in reply to: Need to filter a calculated measure #9821

sjhc1177
Participant
• Started: 16
• Replies: 17
• Total: 33

Hi, it only contains merchandise.

I have a receiving table that has the item details (model, brand…) And I would like to know just the new items that have been sent to stores so I can track how well or poor they are doing.

This would allow us to make more informed discussion on reorders or returns.

Viewing 15 posts - 1 through 15 (of 17 total)