topic Re: Shifting Data 1 Observation down. in QlikView

Shifting Data 1 Observation down.

Tue, 27 May 2014 07:54:21 GMT

Hi I'm wondering how can I shift my data 1 observation down. E.g.

Name NewName

John

Lennon John

Kenny Kenny

Bob Bob

Zack Zack

Let me give a case scenario. For example I want to look at the sales generated by my e-commerce websites based hourly in 4 days.

Website A generates around $50 of sales hourly but occasionally it can generate $20 or even $100. I want to look at the difference of the sales over the Hours and Days and how different are each sales from the previous hour. Also, the expected sale is at least $30. so I'll be using e.g. $50/$30 to see how well is each website doing so an example on pivot chart would be:

Day WebsiteID Hour Sales Difference from previous hour(+-)

12 011 1 50 0

2 20 30

3 39 19

23 90 0

13 011 1 90 0

2 80 10

3 40 40

14 022 1 50 0

2 30 20

3 30 0

15 022 1 230 0

2 180 50

3 86 94

May I know what is the best way to do this. Is creating a new field with all the Sales shifted 1 observation down a good way? Thanks in advance

Re: Shifting Data 1 Observation down.

hic — Tue, 27 May 2014 12:17:11 GMT

You can do this with the Peek() function in the script, e.g.:

Load

Day,

Hour,

Sales,

Sales - Peek(Sales) as Difference

Resident Table

Order By Day, Hour ;

HIC

Re: Shifting Data 1 Observation down.

its_anandrjs — Tue, 27 May 2014 12:50:57 GMT

You can achieve this by the use of Peek() and Previous() functions see the load scripts

//By the use of the Previous function

Raw:

LOAD * INLINE [

Day, Hour, Sales

12, 1, 50

12, 2, 20

12, 3, 39

12, 23, 90

13, 1, 90

13, 2, 80

13, 3, 40

14, 1, 50

14, 2, 30

14, 3, 30

15, 1, 230

15, 2, 180

15, 3, 86

];

Final:

LOAD *,if(Day <> Previous(Day),0, fabs(Sales - Previous(Sales))) as Difference

Resident Raw Order by Day, Hour;

DROP Table Raw;

//By the use of peek functions and use raw file

Final:

LOAD *,if(Day <> Peek(Day),0, fabs(Sales - Peek(Sales))) as Difference

Resident Raw Order by Day, Hour;

DROP Table Raw;

Note:- Fabs is used here to remove - sign if you require this dont put this function.

Re: Shifting Data 1 Observation down.

Wed, 28 May 2014 01:04:43 GMT

Oops I forgot to include that there is another field called Website ID in between Day and Hour!