Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
hello, anyone know a good way to solve this? thanks in advance for your help
Hi,
see attachment.
Regards,
Antonio
see attachment (added 2 charts to Antonio's .qvw)
thanks for help but could you pls copy/passe the script?
Hello,
I made a little change to Antonio's app.
Best regards
Andrea
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
Thanks! i needed the script - this one was a really good one.
you're welcome
regards
Marco
Hi,
Anyone knows how to Compare the prices based on Valid from Date? as a results to see new price and old price comparison.