I am doing a sum of a line in a saved search (item...
# generals
screnshaw
03/15/2022, 7:35 PMI am doing a sum of a line in a saved search (item search joining on transaction). When I don't group, the value is correct. When I group and sum, the value is doubled. Has anyone ran into this before?
z
Zack
03/15/2022, 9:03 PMIf you have the same item on two lines, then it will add the value twice. You could switch to something like average to get around that
s
screnshaw
03/15/2022, 9:05 PMNot sure I follow? And basically need a column to show me which number occurrence this is.
a
AI1
03/15/2022, 9:51 PMAdd transaction.mainline = false to criteria
s
screnshaw
03/15/2022, 9:52 PMThat is there as well.
a
AI1
03/15/2022, 9:53 PMIf you don't group anything does the saved search return 2 rows for each line of the transaction?
s
screnshaw
03/15/2022, 9:53 PMSorry Zack, so the line is only there once. It seems like a bug. It's doubling the value based on the join or something.
If I group it I see double the value. If I ungroup it I see each lines value that I'm looking for. But then when I sum it it does each line's value times two
screnshaw
03/15/2022, 9:53 PMIf I don't group it it returns one line. If I group it it returns that one line but doubles the value
a
AI1
03/15/2022, 9:55 PMDo you have a criteria that is filtering anything using the “contains” operator. Like Item name contains Xxxxxx?
s
screnshaw
03/15/2022, 9:56 PMNope.
a
AI1
03/15/2022, 9:57 PMHow about multi locations? Do you have multiple locations and have a column for location on hand or location avg cost?
(Any of the “location xxxxx” fields)
s
screnshaw
03/15/2022, 9:57 PMI have it filtered to a single location for testing sake
screnshaw
03/15/2022, 9:58 PMIt returns a single line from the criteria. It just doubles the value when I summit
screnshaw
03/15/2022, 9:58 PMSum it
a
AI1
03/15/2022, 9:58 PMHmmm. Do you have a criteria for transaction.type ?
s
screnshaw
03/15/2022, 9:58 PMYup
f
flynn
03/16/2022, 12:16 PMnot sure this helps at all - I think I saw this somewhere but not positive. What if you were to specifically exclude 'tax line' (not sure that it what it is called) in criteria?
s
screnshaw
03/16/2022, 2:03 PMI'm already filtering that line out 😕
screnshaw
03/16/2022, 2:03 PMI've seen a few people on the internet say they've had a similar issue, but no one ever posted a fix
screnshaw
03/16/2022, 2:25 PMOk, so this is an item search. When I look at a Pricing field, it's doubling the sum. Even if I filter on the price that I want. Is there a way to get around this?
a
AI1
03/16/2022, 4:27 PMMaybe add a formula to compare the price level field on the transaction to the pricing name
Like CASE WHEN {pricing.name} = {transaction.pricelevel} THEN 1 ELSE 0 END equals 1
s
screnshaw
03/16/2022, 5:00 PMIt doesn't have a price level on this type sadly
j
Justin
03/23/2022, 2:59 PMDoes your search have results for Sales Team Member, and there are multiple persons listed on the Transaction Sales Team sublist?
2 Views