Hi,
just to elaborate my question yesterday, I attached a file as an example.
1) I want to do the following transformation on TV GRPs column, in the new TV GRPs column (named as New TV GRPs), the first row should be equal to the the first row in the TV GRPs column, the 2nd row in "New TV GRPs" equals to its first row * adstock level" + the 2nd row in "TV GRPs", then the rest of the rows in "New TV GRPs" follow the suit. In this case, the first row in "New TV GRPs" ( 02/01/2006) should equal 0
the second row in "New TV GRPs" (09/01/2006) should equal 0*0.75+75.8 = 75.8
the third row in "New TV GRPs (16/01/2006) should equal to 75.8*0.75+98.42 = 155.27
..
is there a way to do this calculation in Omniscope?
2) I want to lag TV GRPs by 8 period. In this case, the first value in "Lagged TV GRPs" should start from 27/02/2006. is there a way to lag values in omniscope
3) exponential : i have found the formula, i think it should work when the value is inserted.
Many thanks for the help, looking forward to the solutions
Xuan

Xuan,

1.You can't use formulae in Omniscope that refer to cells in the same field as the formula (because of its architecture, Omniscope is not 'row aware').

2. Not sure what you want here; if you want to add 8 weeks to the data field (Field 1), use the DATEADD function (see attached file) to put the later date in the 'Lagged TV GRPs' field. Now you can plot the TV GRPs against the new field with the lagged date.

3. I assume you're ok on this one now.

I hope this helps.

Hello Xuan,

See discussion "http://forums.visokio.com/comments.php?DiscussionID=453", there are an example to obtain values from previous/next rows.

Xuian- I sent you two files showing other approaches to calculations involving using values in other rows defined by differences in time. The formula below, for example, will calculate the monthly % change in a Measure for every unique combination of attributes Product Code and Partner Key:

[Measure] / SUBSET_SINGLETON([Measure], SUBSET3([Month], [Product Code], [Partner Key], DATEADD([Month], -1, "month"), [Product Code], [Partner Key]))

Note that this formula uses Date/Time function DATEADD, so [Month] must be typed Date/Time and be rounded to the month level, rather than carry the days, hours, etc. in the original data field.