Order Value per Region

[Yrstruly2021]

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 

[EdgarOlmos]

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];

[williejacobs]

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)"