TM1 Forum

Guys,

I have an active form that has detailed information by products. I want to create another active forms that consolidates the Products by segments. I am accomplishing this as follows

1. Worksheet 1 has an active form in which I have detailed information by product. in this active form I am also capturing data from 3 other cubes (attributes)

2. Worksheet 2 has another active from by segment and some columns in worksheet 2 is a summary of sourcing from columns in worksheet one. I am using sumif here.

The 2 worksheets work fine in excel, but the moment I deploy the workbook on TM1web the sumif formulas do not work. Is this normal behavior or am I missing something? Also, is there a better way to accomplish point 2 (I do not have the option of creating a reporting cube becuase data in worksheet 1 is calculated on the fly and cannot be stored/calcultaed in a cube)

please advice

I would help you but you need to provide more detail behind your situation. Worksheets 1 and 2, in the same workbook, just separate tabs??? Are you linking a formula from tab 1 to tab 2? Is the linked formula inside the active form part of the report? How can you be sure the row breakdown in tab 1 is EXACTLY the same as tab 2? This kind of information.

tryinghard wrote:Guys,

I have an active form that has detailed information by products. I want to create another active forms that consolidates the Products by segments. I am accomplishing this as follows

1. Worksheet 1 has an active form in which I have detailed information by product. in this active form I am also capturing data from 3 other cubes (attributes)

2. Worksheet 2 has another active from by segment and some columns in worksheet 2 is a summary of sourcing from columns in worksheet one. I am using sumif here.

The 2 worksheets work fine in excel, but the moment I deploy the workbook on TM1web the sumif formulas do not work. Is this normal behavior or am I missing something? Also, is there a better way to accomplish point 2 (I do not have the option of creating a reporting cube becuase data in worksheet 1 is calculated on the fly and cannot be stored/calcultaed in a cube)

please advice

Hi tryinghard,

I've tested your case:
1- Worksheet 1 with data 2- Worksheet 2 (in the same workbook) with data and a "SUMIF" of data from worksheet 1. I've made sure that my sumif range and sumrange include rows from the 1st data row to the last row + 1 (so that when excel rebuilds and thus deletes rows, all rows are still there).

In excell it works and when I import it in TM1Web, I have an "#ERR" value in that cell. So it seems we have the same problem.

Cheers!

Guys,

Thanks a lot for the help. I think I have been able to resolve the problem. But now I have a different problem.

I have 2 active forms in the same worksheet, Calling them A and B. Active form A has a sumif formula that gets value from Active Form B. Now Active Form B should refresh before Active Form A, but since they are on the same workseet how can I ensure that?

Hi
On rebuild the rows get recreated and you lose your references, hence the “REF” error.
So I think that you should be able to solve the problem by using OFFSET (for the range reference) in your function in the second active form. Obviously you also need to COUNT(if) the number of rows in the data range in the first active form. Both functions are supported in TM1Web 9.5.

But generally, and you have stated this as not possible, you should build in an additional dimension (or hierarchies, not sure if that would fix it) in a reporting cube.
Whatever you calculate “on the fly” is most certainly possible in a cube.
So I am wondering what it is you cannot calculate in a cube?

Cheers