Is it possible to use the where clause inside an overclause as below ?
SELECT SUM(amount) OVER(partition by prod_name WHERE dateval > dateval_13week)
I cannot use preceding and following inside over clause as my dates are not in the order. All I need to fetch is the records that are less than 13week date value of the current record.
EDIT :
sum(CASE WHEN dateval >= dateval_13week and dateval <=current_row_dateval then amount else 0 end) over (partition by prod_name order by week_end desc)
Just to elaborate, earlier I was partitioning the records with the below query when I had all my dates in a sequence. Now I have the dates in random order and there are some missing dates.
sum(amount) over
(partition by prod_name order by prod_name,week_end desc rows between 0 preceding and 12 following)
Adding to @D Stanley answer you can use FILTER clause for aggregate function in Postgre:
SELECT SUM(amount) FILTER (WHERE dateval > dateval_13week)
OVER(partition by prod_name)