topic Re: Previous in QlikView

soha1902 — Thu, 24 Sep 2015 05:03:50 GMT

Hi All,

I have following Data:

Load * Inline [
Date,Sales
12/12/2014,50
01/01/2015,100
01/02/2015,200
01/03/2015,300
01/04/2015,500
01/05/2015,600 ];

Tab1:
Load
Date as Date,
Sales as Sales,
Previous(Previous(Sales)) as PreSales
Resident Tab;
Drop Table Tab;

As of now in "Presales" column I am getting values of last 2 days but in future this value will be change for last 5 or some other number.

So please let me the dynamic way which I can use in place of previous.

Re: Previous

sunny_talwar — Thu, 24 Sep 2015 05:39:39 GMT

May be use Peek instead of Previous where you can specify how much back you want to go.

Re: Previous

sunny_talwar — Thu, 24 Sep 2015 05:49:46 GMT

Try this script:

Tab:

Load * Inline [

Date, Sales

12/12/2014, 50

01/01/2015, 100

01/02/2015, 200

01/03/2015, 300

01/04/2015, 500

01/05/2015, 600

];

LET vBack = -3;

Tab1:

Load Date,

Sales,

Peek(Sales, $(vBack)) as PreSales

Resident Tab;

Drop Table Tab;

Here you can set value for vBack before running the script.

Re: Previous

Thu, 24 Sep 2015 05:57:04 GMT

Hi soha1902‌

you can use the code given below for the sum of previous two days.And let me know for more information

Tab1:

Load

Date as Date,

Sales as Sales,

Rangesum(Peek(Sales,-2),Previous(Sales)) as PreSales

Resident Tab;

Drop Table Tab;

Re: Previous

soha1902 — Thu, 24 Sep 2015 06:22:35 GMT

Hi Sunny,

Thanks for you reply. your solution is correct but my requirement is little bit change. Hope you dont mind.

Now I want to sum of all sales for all previous date

For example

if I select 01/05/2015 then Presales would be 1000.

Re: Previous

sunny_talwar — Thu, 24 Sep 2015 06:27:44 GMT

This can be done more easily in front end? Are you looking specifically for a back end solution?

Re: Previous

Thu, 24 Sep 2015 06:34:58 GMT

hi soha1902‌,

you can use the below code for the sum of all the previous days on script.

Tab1:

Load

Date as Date,

Sales as Sales,

RangeSum(Previous(Sales),Peek(Sales,0)) as SumofallPreviousDays

Resident Tab;

Drop Table Tab;

Re: Previous

soha1902 — Thu, 24 Sep 2015 06:38:59 GMT

Yes Back End Solution only

Re: Previous

sunny_talwar — Thu, 24 Sep 2015 06:49:13 GMT

Do you know how much back you would be adding? Is it going to be dynamic?

Re: Previous

soha1902 — Thu, 24 Sep 2015 06:55:21 GMT

As of now 4 only

Re: Previous

soha1902 — Thu, 24 Sep 2015 07:08:17 GMT

it gives only sum of previus(Sales) + First value of sales. but I want sum of previous, plus all the record till first record.

Re: Previous

sunny_talwar — Thu, 24 Sep 2015 07:20:38 GMT

This?

Tab:

Load * Inline [

Date, Sales

12/12/2014, 50

01/01/2015, 100

01/02/2015, 200

01/03/2015, 300

01/04/2015, 500

01/05/2015, 600

];

Tab1:

Load Date,

Sales,

RangeSum(Peek(Sales, -1), Peek(Sales, -2), Peek(Sales, -3)) as PreSales

Resident Tab;

Drop Table Tab;

Re: Previous

SreeniJD — Thu, 24 Sep 2015 07:21:57 GMT

Hi There,

are you looking something like...

RangeSum(Previous(Sales),Peek(Sales)) as PreSales..

which will give you 1000 if you select 01/05/2015..

Happy to help

Sreeni

Re: Previous

Digvijay_Singh — Thu, 24 Sep 2015 07:23:32 GMT

Something like this -

Tab1:

Load

Date as Date,

Sales as Sales,

RangeSum(Sales,Peek('PrevSales',-1)) as PrevSales

Resident Tab;

Drop Table Tab;

Re: Previous

Anonymous — Thu, 24 Sep 2015 07:31:09 GMT

hi soha

Please see below post:

Peek() or Previous() ?

Re: Previous

SreeniJD — Thu, 24 Sep 2015 07:43:21 GMT

Soha -

Are you looking for the desired results as mentioned in below tables (1st refers to presales by year, second one is across years)... Please clarify.

Date	Sales	PreSales
12/12/2014	50	0
01/01/2015	100	0
01/02/2015	200	100
01/03/2015	300	300
01/04/2015	500	600
01/05/2015	600	1100
Date	Sales	PreSales
12/12/2014	50	0
01/01/2015	100	50
01/02/2015	200	150
01/03/2015	300	350
01/04/2015	500	650
01/05/2015	600	1150

Sreeni

Re: Previous

soha1902 — Thu, 24 Sep 2015 07:49:54 GMT

This is correct sunny. I am looking the same but suppose I want to add last 30 days then I need to write 30 Peek(). How I will do the same in dynamically.

Thanks.

Re: Previous

sunny_talwar — Thu, 24 Sep 2015 07:51:06 GMT

For dynamic solution, you might have to use Loop. I don't think there is an easier solution.

Best,

Sunny