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?

[arulsettu]

post your code

[gautik92]

post sample data

[#4]

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)

[stigchel]

Probably because the if condition is not using the same set analysis so the results for the if condition change when making a selection. Use the same set analysis for the if with the Only function, something like

If(Only({<FieldA=, FieldB=, FieldC=p(FieldD), FieldE=p(FieldF)>} FieldX)='True'

, aggr(sum({<FieldA=, FieldB=, FieldC=p(FieldD), FieldE=p(FieldF)>}Counters, KeyID))

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

[sudeepkm]

you said that when you select values form Field A the chart is showing results is that happening for Field B too?

[#7]

Hi,

Yes it happens to the other fields that I set as ignore as well. But the weird thing is I have the whole same expression and dimension on a different value (e.g. KeyID2) and there it works perfectly.

When removing the if statement, no improvement.

Thanks,

Neal

[#8]

I have figured out what piece of code causes it. It is one of the FieldC=p(FieldD). If I remove this the ignoring of the wanted fields work again. These fields are key fields in 2 dimension tables. Why could this cause trouble?

[stigchel]

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))'}

[tresesco]

When you select in a field say FieldX, the possible values of FieldC might change, because there could be no transaction of FieldX for that FieldD(eventually FieldC) value. Therefore, selecting in FieldX might cause change in the set result even though you include it in the set.

Hope that makes sense.