Previous Week Total

[Matt_Leonard]

Hi

I have a straight table with products, sale dates, prices and grade.  The app is filtered by year and month and what I am looking to do is to show against each row the average sales price for the previous week, for example:

Product          Sale Date           Price          Grade

    A                19/04/2024          £5,000          3         

On this row, I need to show the average sales price for the previous week (08/04/2024 - 14/04/2024) for all sales with Grade 3.

I need this to be dynamic so that if a user selects a different month, it will calculate the previous week based on the updated straight table.

I have managed to calculate the formula based on previous yearmonth but am stuck on changing it to previous week.

Any help would be appreciated.

Kind regards

Matt

[BenjaminT · Accepted solution]

Not sure if I've understood correctly, I think you can use a combination of aggr() and above() functions to achieve this, provided your data is loaded in ascending order of [Sale Date].

Sample load script, with additional numerical representation of the Sale Week:

LOAD *,
week([Sale Date]) as [Sale Week]
;
LOAD * INLINE [
Product, Sale Date, Price, Grade
A, 12/04/2024, 23000, 3
B, 19/04/2024, 500, 3  
A, 19/04/2024, 5000, 3  
A, 19/04/2024, 8000, 3  
A, 19/04/2024, 2000, 2
B, 25/04/2024, 1000, 3  
A, 25/04/2024, 4000, 3  
A, 25/04/2024, 4500, 2
A, 25/04/2024, 2000, 3  
];

Then add a measure column to your table with:

aggr(nodistinct above(avg(Price)), Product, Grade, [Sale Week])

[Matt_Leonard]

Worked like a charm, thanks very much!