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

[its_anandrjs]

Hi Sbuzi,

As i understand your requirement you want to create a pivot table and when you select one of this five formulas according to that your pivot table should work am i right please confirm me, if so in this case you need to create different formulas and on selection you need to activate that formulas.Please confirm me.

And why you want to use excel file for formulas.

Regards,

Anand

[#3]

Hi Anand,
in excel file attached there are 5 different formulas. My problem is to create a table (see Table "Sheet 1" in the example) where you can see in the table "Table Overall," the five formulas simultaneously.
Now it only works if I select a formula. For example, the formula only works if I select 4.

I use an Excel file because I have many different formulas to be used together.

Thank

[stevedark]

Because of where QlikView evaluates the $(=) expression it can not be aware of the dimension.  I therefore don't believe the approach you have gone for will work.

What can work is having multiple expressions in a chart, each one picking up an expression from the spreadsheet.

The first expression will be:

$(=maxstring({1<IdFormula={1}>}MathematicalFormula))

You will then need to increment the {1} for each extra expression - up to the maximum number of expressions you expect to have.

For the extra expressions you will want to make them only display when a value exists.  This is done on the presentation tab.  This will be something like:

=count({1<IdFormula={1}>}IdFormula) > 0

Again you will need to increment the {1} for each expression.

If you have any problems please give me a shout.

Steve

Quick Intelligence

[johnw]

Attached is one way to handle table-driven calculations by using the table to generate an expression that picks the correct expression from a list.

[#6]

Thanks John,
I tried to use the example that you have attached, but I can not get it to work.
Where is error?
I attach the example that I did

Thanks

[its_anandrjs]

Hi Sbuzi,

See the attached sample by this you can use it by a list, it is ok if you use formulas in the list.

Regards,

Anand

[#8]

Hi Anand,

Thanks for your help,
But I did not understand yet.

In Formula2.xls files (see attached file) there are 5 types of formulas:

Count(distinct if(IdSeat='A' and IdOutcome='P',IdRow))

Count(distinct if(IdSeat='B' and IdOutcome='P',IdRow))

Count(distinct if(IdSeat='A' and IdOutcome='N',IdRow))

Count(distinct if(IdSeat='B' and IdOutcome='N',IdRow))

Count(distinct if(IdSeat='C' and IdOutcome='N',IdRow))

With the input data , I would get a pivot table type:

    Dimension1       Dimension2        Expression

      (IdSeat)             IdOutcome)           Count(.....

          A                      P                      5

          A                      N                      2

          B                      P                      1

          B                      N                      3

          C                      N                      1

Thank All

[its_anandrjs]

Hi Sbuzi,

You need to convert pivot table to straight table and then see it is you requirement that you want.

Thanks,

Anand

[#10]

Hi Anand,
The table in the example that you have attached is different from the example of a table in my previous post.
In your chart, the value of the last column is always the same, in my table, the value should be different and depend on the value of the first and second dimension as indicated in the mathematical expression of the file Formula2.xls

Thank All