Excel cell reference facility

[tresesco]

Hi All,

I have a requirement of generating a report which was previously in EXCEL format. The formulas were intensively cell and sheet reference based. That is, when i calculate a new column it is again required to refer its total or the column in other sheet or in the same PIVOT TABLE.

now i am facing chllenges in the following issues:

- Reference to the TOTAL of calculated column (AGGR function or TOTAL not helping)

- Refering one pivot table column in another pivot table.

Any help is greatly appreciated.

Regards,

tresesco

[montubhardwaj]

I think you can use Variable approach. Create variables and use them to populate your columns. This way, later you can refer to columns. Also, you can use PEEK function for referring to particular record.

Thanks,

Sharma

[Oleg_Troyansky]

tresesco,

your question and a description of your problem is too general... You'd need to get into more specifics for anyone to be able to make a suggestion.

Generally speaking, you can't refer to Summarized totals of pivot tables - not within the same Pivot Table, and definitely not between different Picot Tables.

The "book" answer is to use AGGR, and it should help if you know how to use it.

If you'd like to post a specific example, we can try and solve it...

thanks!

Oleg

[tresesco]

Thank you Oleg.

here i am trying to explain in a better way. i have a pivot table(PIVOT1) with many calculated dimensions(Col1, col2, col3.........). Each of these dimensions(columns) has very lengthy formulas. in another pivot table(PIVOT2) i have other calculated dimensions(columns) with big formulas.

Now i need to calculate a new field using formula like - SubTotal of Col2(PIVOT2) / Col1(PIVOT1) in the PIVOT1................... Note: i don't want to re-write the formula again for SubTotal of Col2(PIVOT2) ...as it is very long and tidious..................

IS THERE any way? By the way, i have got to calculate Subtotal using AGGR function.

[Oleg_Troyansky · Accepted solution]

You don't need to re-write the formula... besides the usual "copy-paste", you can export and import expressions, or you can use variables to hold the expression formulas and recall the variables using $() - expansions. For example:

If Variable vExpr1 = 'sum(Sales)', you can reuse it many times by referencing the variable =$(vExpr1). QlikView will substitute the variable name by the formula and evaluate it every time.

Notice that the same syntax rules apply even when you use the variable - you just save the keystrokes using the variable. That means, for example, that an expression

sum($(vExpr1)) won't be valid without using the AGGR function.

enjoy!