Previous Term Average using Set Analysis

[#1]

Hi All,

Please help me with the following requirement which is very much required to be solved.

We have to calculate the Previous Term Average and use it for calculation.

following is required

Year Term Amount Previous Term Average

2009 2009 1st Term 100 -

2009 2nd Term 200 100/6

2010 2010 1st Term 300 200/6

2010 2nd Term 400 300/6

So when 2009 2nd term is selected, the average of previous term has to be used for the calculation.

I have to acheive the above requirement without the use of Above() Function. Can this be done using SET Analysis?

Please help me in this regard.

[#2]

hi,

try something like this:

test:
LOAD makedate(left(MY,4),Right(MY,2)) as Date,
Term,
Amount
FROM
test.xlsx
(ooxml, embedded labels, table is Sheet1);

store test into test.qvd;

drop table test;

test:
LOAD Date,
Term,
Amount
FROM
test.qvd
(qvd);

calendar:
LOAD Date,
Year(Date) as Y,
Month(Date) as M,
monthname(Date) as MY
FROM
test.qvd
(qvd);

best regards!

[#3]

That works like Charm.............Awesome!!!

Thank you very very very very much!!!...........you rock 🙂