Formulas of expression by an external file

[#1]

Hello everyone.

I should make a pivot table where the column expression contains a count that is provided by a formula like this:

Count (if (Type 1 = 'A' and Date1>= xxxx, IdRow))

Because I need to set many different formulas, I decided to create an external file (Excel) in which the code and the formula is, of course, for each code have a specific formula.

I built a Pivot table where I put the command in the expression

$(=MathematicalFormula) and I only work whenI select a (only 1) code from a list box.

I can’t not run thePivot Table formulas all at once.

Anyone can helpme?

See the attached files.

Thanks All

[jonathandienst]

John

That's brilliant - I was looking for something like this for a dynamic calculation engine! And it works better than I expected - I have about 25-30 complex expressions to load in this way.

Regards

Jonathan

[#3]

Hi Jonathan,

You have no idea how I can do to solve my problem.

Thanks

[stevedark]

HI there - I think you headed off down a much more complicated path than you needed to.  You simply need to add a count field to your data table and sum that.

Also, the loading and dropping of your resident table was not required as you could add the IdRow field on when the table was loaded from the inline.

You can get to the output you posted in your message above without using the formula spreadsheet at all.

You will not need to do anything as complicated as what you were attempting unless you need to build something very complex or flexible.

Attached is an example of the simple way of counting rows against different dimensions.

[#5]

Hi Steve,
thanks for your answer.
Unfortunately my problem is more complicated.
I have a set of formulas (about 50), each very complicated.
Each formula is a SQL command that does a particular count.
I would like to create a unique table that in every line I have the result of the formula.

Thank All

[jonathandienst]

Sbuzi

That sounds exactly like what John Witherspoon posted earlier. Did you check that your expressions where correct?

Count(Distinct If(IdSeat = 'A' And IdOutcome = 'P', IdRow)) does not work as there is no IdRow field in your example

(and should probably be Count(If(IdSeat = 'A' And IdOutcome = 'P', Distinct IdRow))), and the IdSeat comparison is unnecessary given that IdSeat is a dimension in your table.

I am assuming that the expressions in your example are not representative of your problem.

Regards

Jonathan