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

    Hi, additional thought.

    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:

     

    1YR AGO TRANS# DISTINCT COUNT:=CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]>0,dateadd(dCalendar[DATES],-364,DAY))-CALCULATE(DISTINCTCOUNT(‘SALES TABLE'[TRANS#]),’SALES TABLE'[UNITS SOLD]<0,dateadd(dCalendar[DATES],-364,DAY))

     

    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’ve never used “ADDCOLUMNS”, I must read up on it.

    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.
    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)
    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;

    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

    Hi, thanks for your reply.

     

    I’ve found your site to be very helpful.

     

    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)