Trend chart not showing on week's result

Get Help
1

I have data as follows

image
image621×527 17.2 KB

The trend line chart doesn't show up using week.

image
image1274×394 11.2 KB

If I group by day, then it works. Can I modify it to work with week?

2

This is the query

Select  CONCAT(YEAR(DATE_ADD(created_at, INTERVAL 5 HOUR)), '-', WEEK(DATE_ADD(created_at, INTERVAL 5 HOUR))) AS week_name,  round(sum(Revenue)) as Revenue
From
(SELECT order_currency_code, subtotal, created_at, base_discount_amount, 
Case
when order_currency_code='PKR' Then (subtotal + base_discount_amount ) 
when order_currency_code='USD' Then (subtotal + base_discount_amount) * 175.29
when order_currency_code='AED' Then (subtotal + base_discount_amount) * 47.72
when order_currency_code='GBP' Then (subtotal + base_discount_amount) * 242.32
when order_currency_code='CAD' Then (subtotal + base_discount_amount) * 141.90
END AS `Revenue`
From sales_order
WHERE  (status='complete' or status='processing' or status='pending') and MONTH(DATE_ADD(created_at, INTERVAL 5 HOUR)) = MONTH(CURRENT_DATE()) AND YEAR(DATE_ADD(created_at, INTERVAL 5 HOUR)) = YEAR(CURRENT_DATE())
) as derived
Group by week(DATE_ADD(created_at, INTERVAL 5 HOUR))
Order by week(DATE_ADD(created_at, INTERVAL 5 HOUR))
3

Hi @Anum
Metabase does not understand that week format. You'll have to return a date. Try using the GUI and see what SQL is generated.