Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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])
Worked like a charm, thanks very much!