Set Analysis ignore not working?

[#1]

Hi All,

Maybe I am missing something obvious but I have been looking for a while without finding a satisfying explanation.

I have a pivot table with 2 dimensions (1 calculated) and an expression. The expression and calculated dimension both use set analysis ignoring field A (using field A=). But for some reason when I select a value in listbox list A, the values still change. Why..?

Any ideas?

[#2]

How would this FieldC=p(FieldD) remove the effect of FieldA=, FieldB=?

Dimension: if(FieldX='True', aggr(sum({<FieldA=, FieldB=, FieldC=p(FieldD), FieldE=p(FieldF)>}Counters, KeyID))

Expression: count({<FieldA=, FieldB= FieldE=p(FieldF)>} distinct KeyID)

[#3]

Maybe somebody knows a better solution. The FieldC=p(FieldD) was used to identify specific records.

Situation:

When a purchase is made by a person, that purchase gets a unique record (e.g. FieldPurchaseID) which links to that person (e.g.FieldPersonID), but also a unique purchase (e.g. FieldPurchaseID) is added to that persons company (e.g. FieldStructureID). The fields FieldPersonID and FieldStructureID are unique identifiers in 2 different dimension tables that are 1-1 with key fields (e.g. %FieldPersonID and %FieldStructureID), which link to another table.

So a purchuse by a person creates the records (1st to Person, 2nd automatically created for Structure):

Record     FieldPurchaseID, FieldPersonID, FieldStructureID, %FieldPersonID, %FieldStructureID

1              abz17                   Person1727         Structure323     Person1727          Structure323

2              abz18                   -                         Structure323     Structure323          Structure323

I want to be able to count both record seperately. Now I have used to count second (to structures) via %FieldPersonID=p(%FieldStructureID). If this causes the problem, how else could I count this?

Thanks

[stigchel]

Have you seen my answer above? Instead of the p(Field), use a concatenated list of posibble values. In the concat function you can use the same set analysis to ignore selctions in e.g. FieldA. Above answer:

What if you replace the FieldE=p(FieldF) in the set (and others) with

FieldE={'$(=chr(39) & replace(concat({<FieldA=, FieldB=>} distinct FieldF, '|'), '|', chr(39) & ',' & chr(39)) & chr(39))'}

[#5]

Hi Piet Hein,

I'm not sure I understand what your code will do. Could you elaborate how this will only select record 2 as I defined above?

Many thanks!

Neal

[stigchel]

It is basically building a set value list e.g.

FieldE={'Value1','Value2'}

The concat builds this list of possible values and uses the same set analysis to ignore the selections in e.g. FieldA

The replace just takes care of the necessary delimiters in a set value list

This all wrapped in a so called dollar sign expansion (see help), which is replaced by its result before the rest of the expression is evaluated