Announcements
July 15, NEW Customer Portal: Initial launch will improve how you submit Support Cases. IMPORTANT DETAILS
cancel
Showing results for
Did you mean:
Contributor II

## Previous Week Total

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:

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

Labels (3)

• ### Set Analysis

1 Solution

Accepted Solutions
Partner - Creator

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

``````LOAD *,
week([Sale Date]) as [Sale Week]
;
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
];``````

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

2 Replies
Partner - Creator

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

``````LOAD *,
week([Sale Date]) as [Sale Week]
;
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
];``````

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