QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Not applicable

How to compare price at two different time intervals

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

1 Solution

Accepted Solutions
MVP

Re: How to compare price at two different time intervals

Hi,

one solution could be:

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

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

tabTimes:
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 Replies
Not applicable

Re: How to compare price at two different time intervals

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

Honored Contributor III

Hi,

see attachment.

Regards,

Antonio

MVP

Re: How to compare price at two different time intervals

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

Not applicable

Re: How to compare price at two different time intervals

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

Contributor II

Re: How to compare price at two different time intervals

Hello,

I made a little change to Antonio's app.

Best regards

Andrea

MVP

Re: How to compare price at two different time intervals

Hi,

one solution could be:

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

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

tabTimes:
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

Not applicable

Re: How to compare price at two different time intervals

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

MVP

you're welcome

regards

Marco