Calculated fields: total sales by customer, by brand, by year

Epicor ERP 10
, ,
1

Hello again, EpiUsers.

I’m trying to retrieve a table showing
a list of our customers
beside the total dollar amount ordered
in products of one brand
ordered in the years 2019, 2020, and 2021(YTD).

I have assumed that compiling these sales totals by customer, by brand, by year
is best accomplished using a SubQuery, containing Calculated Fields.

Right now, I’m using this general syntax in each of 3 Calculated Field editors:

case when OrderHed.OrderDate like ‘%2019’ and Part.Brand_c = ‘BRAND’ then sum(OrderDtl.OrderQty) * sum(OrderDtl.UnitPrice - OrderDtl.Discount) else 0 end

When I run the query, I see the columns I want.
But the Total 2019, Total 2020, and Total 2021 columns show values of 0.00 all the way down.
So I suspect the issue lies in the Calculated Fields.

Is there another way to phrase the aggregate functions more effectively?

2

@Riggs:

This might be an issue with order of operations. Try adjusting the formula following the “then” phrase as:

sum(OrderDtl.OrderQty * (OrderDtl.UnitPrice - OrderDtl.Discount))

In general, the calculation should be done row by row first, then aggregate.

hope this helps
Matthew Morgan

1 Like
3

try this

sum(case when OrderHed.OrderDate like ‘%2019’ and Part.Brand_c = ‘BRAND’ then (OrderDtl.OrderQty) * (OrderDtl.UnitPrice - OrderDtl.Discount) else 0 end)

sum is the first wording, then what is composed of the sum is your case statement

But just to make sure, I am createing a calculated field that would show the year portion of the date…
datepart if I remember…

Pierre

1 Like
4

This is correct, you want to do the aggregate last, after the CASE evaluation and arithmetic functions.

1 Like