Hi there,
I would like to find the most efficient way, most likely a macro, to calculate the 52 week high and low.
Currently, I am limited to bringing in the data source twice which looks something like:
Transport client Date Quote
airplane Commercial 01/02/17 115.60
truck private 01/02/17 150.32
The data I work with is not necessarily of the same frequency e.g. daily, weekly, monthly etc. Therefore the row count approach is not feasible.
What I did, was to bring the data source in twice, and for each Date by transport and client, I have joined it back to itself so that for each date I have all the dates for that transport type/client. Obviously this creates enormous levels of duplication and processing is rather difficult.
At this stage, I then introduce a datetimediff calculation between the left date and the duplicated right date ("available date") and then filter on the datetimediff which should be less than 364 days ("52 weeks") and more than -1.
I am then left with 52 weeks worth of Date for each date and I can then do a max and min summary for each transport/client.
The information obtained is then joined back to the initial source.
This approach worked very well on a test dataset, but it is not processing very well once adapted on a real-life size database.
Can anyone suggest a better approach please?
the ideal output would be:
Transport client Date Quote 52 week min 52 week high
airplane Commercial 01/01/17 112.90 112.90 112.90
truck private 01/01/17 120.70 120.70 120.70
airplane Commercial 01/02/17 115.60 112.90 115.60
truck private 01/02/17 150.32 120.70 150.32
Regards,
Denise
