Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
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

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

1 Solution

Accepted Solutions

Re: How to compare price at two different time intervals

Hi,

one solution could be:

QlikCommunity_Thread_161728_Pic1.JPG

QlikCommunity_Thread_161728_Pic2.JPG

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

antoniotiman
Honored Contributor III

Re: How to compare price at two different time intervals

Hi,

see attachment.

Regards,

Antonio

MVP
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?

anlonghi2
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

Re: How to compare price at two different time intervals

Hi,

one solution could be:

QlikCommunity_Thread_161728_Pic1.JPG

QlikCommunity_Thread_161728_Pic2.JPG

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

Not applicable

Re: How to compare price at two different time intervals

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

Re: How to compare price at two different time intervals

you're welcome

regards

Marco

Community Browser