TM1 Forum

Hi guys - Am new in this forum, so if i have posted in the wrong space please direct me.

I want to develop an Active report that compares a selected week of data with the previous week last season. I have a period dimension and one of the consolidations is Week begining which contains days of the week child elements beginning Monday to sunday. Example "WB 2012-Jun-18" will be compared to "WB 2011-Jun-13"

I have created 2 active forms that with dynamic subsets that gets generated by a TI process. 1 of the active forms contains the selected week and the other has comparative week.
Note: the measures are the same on both forms.

My Challenge/ Probblem
1- I want to create a third report (a separate sheet in the same workbook) that compares data by day in both forms and returns the variance for the user.
2- 1 day might return 2 rows for this year but 1 row for last year and vice versa - as such the report will need to group by day.

Hope you can help - i have attached an image of the report so hope it helps when assisting.

I don't know about the others but your image doesn't really explain anything about your questions. Nevertheless, what you want to do is quite easy. Instead of worrying about dynamic subsets for this year and last year, you need to build the report based on the time periods in the current year and then pull back the corresponding periods from the prior year based on an attribute you can add to your time dimension, called "PreviousPeriod" or something like that. Build your active form to pull back the current year. Then add columns to the right with DBRAs, DBRWs to pull back the data from the previous period, and then Excel formulas to subtract current from actual. Make sure you expand the active form range name areas to include your new columns. As long as you assign a PreviousPeriod attribute to each time element this should work for any periods you are using, days, weeks, months, quarters, tec.

Thanks Tomok for the quick response - If i understand your proposed solution correctly, it requires that i modify the period dimension by adding an attribute "Previousdate"? The problem with that is the comparative date will change depending on the user and as such can not be dynamically added as an attribute (unless you are telling me its possible to dynamically add an attribute in a dimension).

Thanks.

chiefouko wrote:the comparative date will change depending on the user

You didn't mention anything about that in your original post. Quite the opposite, it sounded like for each period there will be a well known 'previous period' defined a priori.

In that case just give users a parameter cell (a SUBNM, a drop-down or even just a free text cell) where they will be able to select any date they want to be compared with the 'current period'. Your DBRW formulas for the 'previous period' section will then need to reference that parametrising cell to get the expected period.

Alternatively, if the requirement is for your model to 'know' the 'previous period' automatically, you could build a 3-dimensional cube containing the }Clients dimension and the Period dimension and a Measure dimension and store the 'previous periods' on a per-user basis in that cube and just pull them from there into the report. If you use the worksheet function TM1USER() then the worksheet will be able to take the right settings for the right user from that cube.