topic Re: Order Value per Region in App Development

Order Value per Region

Yrstruly2021 — Thu, 03 Nov 2022 13:52:20 GMT

Please assist. I want to answer this question: "What is the average order value in the African Region per month?"

My Data Model:

My Code:

SELECT Avg(Orders) AS Order Value

FROM Orders, Customer, Nation, Region

Join Customer AS C

ON Customer.Custkey = Orders.Custkey

Join Customer AS C ON Customer.Nationkey=Nation.Nationkey

Join Nation.Nationkey=Region.Nationkey

where Region = Africa

https://drive.google.com/file/d/1RxutoJHdoVBvP21FnnuoBHFe_SZAnCpE/view?usp=sharing

Re: Order Value per Region

EdgarOlmos — Fri, 11 Nov 2022 00:16:39 GMT

Hi.
I didn't understand very well the problem.
I send you an idea for how to solve the query.
I hope this script can help you.
Regards

AVG_TMP:
Load *,
Month(ORDERDATE) as [MONTH ORDERDATE]
;
SQL SELECT A.TOTALPRICE, A.ORDERDATE AS Order Value
FROM Orders as A Join Customer AS B
ON B.Custkey = A.Custkey
JOIN Nation as C
ON C.Nationkey=B.Nationkey
JOIN Region as D
ON C.Regionkey=D.Regionkey
where D.Name = 'Africa' ;

AVG:
Load
[MONTH ORDERDATE],
avg(TOTALPRICE) as [AVG TOTALPRICE]
Resident AVG_TMP
Group by [MONTH ORDERDATE];

Re: Order Value per Region

williejacobs — Fri, 11 Nov 2022 07:14:43 GMT

Hi @Yrstruly2021

I have not downloaded your data, but I am sure based schema you should be able to get to your required answer trying the following.
Create a month field in your script, as suggested by Edgar.
I would do it this way, "Date(MonthStart(ORDERDATE), 'MMM yyyy') as yearmonth".

On your selected chart, add the yearmonth field as dimension and create a measure for your average order value.
"sum ( {$<Region={Africa}>} OrderPrice) / Count( {$<Region={Africa}>} distinct orderkey)"