topic Getting Previous in QlikView

Getting Previous

Tue, 21 Oct 2014 08:53:08 GMT

Hi Guys,

Just want to ask if how can I do this using set analysis:

		Year	2013										2014
		Qtr-Year	Formula1	Formula2	Formula1	Formula2	Formula1	Formula2	Formula1	Formula2	Formula1	Formula2	Formula1	Formula2
Type	Customer	Region	1Q13	1Q13	2Q13	2Q13	3Q13	3Q13	3Q13	3Q13	4Q13	4Q13	1Q14	2Q14
Rev	A	R1	1231	0	5435	1231	123	5435	213	123	23	213	23432	23
	B	R1	12312	0	34534	12312	213	34534	213	213	23	213	2342	23
	C	R2	1231	0	345	1231	23	345	12312	23	12323	12312	23423	12323
	D	R2	2353	0	213	2353	213	213	123	213	232	123	23432	232

I have here a table. Formula1 is just a sum of Revenue, however for Formula2, I need to get the PREVIOUS sum of revenue.

ex for 2Q13:

Formula1 : sum(Rev)

Formula2 = Formula1 of the last quarter

I do not want to use the before() function. Is it possible to be achieve using set analysis only?

Thanks,

Lyn

Re: Getting Previous

Gysbert_Wassenaar — Tue, 21 Oct 2014 09:39:56 GMT

No, it's not possible with set analysis alone. In fact, I don't think it is possible without modifying the script that loads the data so each quarter is associated with the previous quarter.

Re: Getting Previous

Tue, 21 Oct 2014 12:37:01 GMT

how about if I use the before() function? I tried using it but the 1st quarter of every year is always null..

Re: Getting Previous

Anonymous — Tue, 21 Oct 2014 12:49:38 GMT

Try using above function

Re: Getting Previous

tresB — Tue, 21 Oct 2014 13:03:08 GMT

This issue could be overcome using above() in combination with Aggr(). Have a look here: Calculating rolling n-period totals, averages or other aggregations

Re: Getting Previous

Thu, 23 Oct 2014 05:13:15 GMT

can you give me an example?

Re: Getting Previous

tresB — Thu, 23 Oct 2014 05:41:10 GMT

Help us help you. Create a sample qvw (and share) what we can work on and help you.