How to compare price at two different time intervals

[#1]

Hello experts

I have couple of questions in regards to time/ranges.

What I have is this:

Column:

Time

07:00:00

08:00:00

09:00:00

etc..

Column:

Price

1400

1410

1390

etc

1) I need to know if the price at 07:00:00 was Higher then the price at 16:00:00?

2) then I need to calculate the difference - by how many points

3) and then I need to build time ranges and find an average price for each interval:

range: 'morning' - time: 07-12

range ' lunch' - time 12-13

range 'evening' -  time 13-16

Can you please help me with that. Thanks in advance for your help!

[#2]

hello, anyone know a good way to solve this? thanks in advance for your help

[antoniotiman]

Hi,

see attachment.

Regards,

Antonio

[maxgro]

see attachment (added 2 charts to Antonio's .qvw)

[#5]

thanks for help but could you pls copy/passe the script?

[anlonghi2]

Hello,

I made a little change to Antonio's app.

Best regards

Andrea

[MarcoWedel · Accepted solution]

Hi,

one solution could be:

tabPrices:
LOAD *,
    Time(Round(frac(DateTime),'01:00:00')) as Time,
    Money(100+Ceil(Rand()*10)) as Price,
    'Product '&IterNo() as Product
While IterNo() <= 3;
LOAD *,
    Timestamp(Date+'07:00:00'+(IterNo()-1)*'01:00:00') as DateTime
While IterNo()<=10;
LOAD Date(Today()-30+RecNo()) as Date
AutoGenerate 30;

Left Join (tabPrices)
LOAD Product,
    Date,
    Money(FirstSortedValue(Price,-Time)-FirstSortedValue(Price,Time)) as PriceChange
Resident tabPrices
Group By Product, Date;

tabTimes:
LOAD Round(Time, '01:00:00') as Time,
    range
Inline [
Time, range
'07:00:00', morning
'08:00:00', morning
'09:00:00', morning
'10:00:00', morning
'11:00:00', morning
'12:00:00', lunch
'13:00:00', afternoon
'14:00:00', afternoon
'15:00:00', afternoon
'16:00:00', afternoon
];

hope this helps

regards

Marco

[#8]

Thanks! i needed the script - this one was a really good one.

[MarcoWedel]

you're welcome

regards

Marco