Click here to Skip to main content
15,563,228 members
Home / Discussions / Visual Basic
   

Visual Basic

 
AnswerRe: Install a VB Net application to install a sql database on other computers Pin
evry1falls6-May-20 10:22
evry1falls6-May-20 10:22 
QuestionLinq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx3-Apr-20 8:48
professionaljkirkerx3-Apr-20 8:48 
AnswerRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx3-Apr-20 9:51
professionaljkirkerx3-Apr-20 9:51 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
Richard Deeming6-Apr-20 2:19
mveRichard Deeming6-Apr-20 2:19 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx6-Apr-20 8:28
professionaljkirkerx6-Apr-20 8:28 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
Richard Deeming6-Apr-20 9:01
mveRichard Deeming6-Apr-20 9:01 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx6-Apr-20 10:20
professionaljkirkerx6-Apr-20 10:20 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
Richard Deeming7-Apr-20 1:30
mveRichard Deeming7-Apr-20 1:30 
jkirkerx wrote:
VB.NET
If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
If FCOST or FSHIPQTY is equal to 0, then FCOST * FSHIPQTY will return 0. There's no need for the conditional here, or any of the similar conditionals.
jkirkerx wrote:
VB.NET
Select Case True
    Case item.FPRICE.Equals(0)
        ' pI.FExtMargin -= If(item.FCOST <> 0 And item.FSHIPQTY <> 0, item.FCOST * item.FSHIPQTY, 0)
        ' Removing the conditional:
        pI.FExtMargin -= item.FCOST * item.FSHIPQTY
        
    Case Else
        ' pI.FExtMargin += pI.FExtPrice - pI.FExtCost
        ' Expanding the calculations:
        ' pI.FExtMargin += (item.FCOST * item.FSHIPQTY) - (item.FPRICE * item.FSHIPQTY)
        pI.FExtMargin += (item.FCOST - item.FPRICE) * item.FSHIPQTY
End Select
If FPRICE is equal to 0, then FCOST * FPRICE is equal to FCOST. Both cases are trying to perform the same calculation, so the Select Case is not required.

I say "trying to", because you're actually inflating the margin. For each item in the invoice which has a price, you're adding the cumulative difference between the price and the cost, rather than the difference between the price and the cost of that item.

Eg:
Items:
------
1: Price = 10; Cost = 5; Qty = 1
2: Price = 10; Cost = 5; Qty = 1
3: Price = 10; Cost = 5; Qty = 1

Your calculation:
-----------------
1: FExtPrice = 10; FExtCost =  5; FExtMargin =  0 + 10 -  5 =  5
2: FExtPrice = 20; FExtCost = 10; FExtMargin =  5 + 20 - 10 = 15
3: FExtPrice = 30; FExtCost = 15; FExtMargin = 15 + 30 - 15 = 30

Total margin: 30 = 100% !

Correct calculation:
--------------------
1: FExtPrice = 10; FExtCost =  5; FExtMargin =  0 + 10 - 5 = 5
2: FExtPrice = 20; FExtCost = 10; FExtMargin =  5 + 10 - 5 = 10
3: FExtPrice = 30; FExtCost = 15; FExtMargin = 10 + 10 - 5 = 15

Total margin: 15 = 50%

This code should produce the correct results in a single statement, without needing to manually loop over the items:
VB.NET
Dim gInvoicesAll As List(Of MarginCustomerInvoicesR1) = cTpAll.OrderBy(Function(ob) ob.FINVNO).GroupBy(Function(inv) inv.FINVNO).Select(Function(cl) New MarginCustomerInvoicesR1() With {
 .FCustNo = cl.First().FCUSTNO,
 .FInvNo = cl.First().FINVNO,
 .FShipDate = cl.First().FSHIPDATE,
 .FDescript = cl.First().FDESCRIPT,
 .FAmount = cl.Sum(Function(item) item.FAMOUNT),
 .FExtCost = cl.Sum(Function(item) item.FCOST * item.FSHIPQTY),
 .FExtPrice = cl.Sum(Function(item) item.FPRICE * item.FSHIPQTY),
 .FExtMargin = cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY),
 .FPercent = If(cl.Sum(Function(item) item.FCOST * item.FSHIPQTY) = 0, 0, Math.Round(cl.Sum(Function(item) (item.FPRICE - item.FCOST) * item.FSHIPQTY) / cl.Sum(Function(item) item.FCOST * item.FSHIPQTY), 4))
}).ToList()
If it doesn't, then give me an example where it goes wrong.



"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx7-Apr-20 8:05
professionaljkirkerx7-Apr-20 8:05 
GeneralRe: Linq GroupBy Invoice Number, iterate all items before group Pin
jkirkerx8-Apr-20 10:24
professionaljkirkerx8-Apr-20 10:24 
QuestionHow to update sql server data from excel data using VBA code by all User Pin
Member 1478441627-Mar-20 1:25
Member 1478441627-Mar-20 1:25 
AnswerRe: How to update sql server data from excel data using VBA code by all User Pin
ZurdoDev27-Mar-20 2:19
professionalZurdoDev27-Mar-20 2:19 
GeneralRe: How to update sql server data from excel data using VBA code by all User Pin
Member 1478441627-Mar-20 3:17
Member 1478441627-Mar-20 3:17 
Questionevent handler Pin
Member 1356965022-Mar-20 0:16
Member 1356965022-Mar-20 0:16 
AnswerRe: event handler Pin
Richard MacCutchan22-Mar-20 0:31
mveRichard MacCutchan22-Mar-20 0:31 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 1:24
Member 1356965022-Mar-20 1:24 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 1:33
mveRichard MacCutchan22-Mar-20 1:33 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 2:04
Member 1356965022-Mar-20 2:04 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 5:03
mveRichard MacCutchan22-Mar-20 5:03 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 5:18
Member 1356965022-Mar-20 5:18 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 5:47
mveRichard MacCutchan22-Mar-20 5:47 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 6:01
Member 1356965022-Mar-20 6:01 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 6:12
mveRichard MacCutchan22-Mar-20 6:12 
GeneralRe: event handler Pin
Richard MacCutchan22-Mar-20 6:11
mveRichard MacCutchan22-Mar-20 6:11 
GeneralRe: event handler Pin
Member 1356965022-Mar-20 6:48
Member 1356965022-Mar-20 6:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.