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

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?


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

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…


1 Like

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

1 Like