topic Re: Set Analysis ignore not working? in QlikView

Set Analysis ignore not working?

Wed, 07 Oct 2015 12:08:43 GMT

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?

Re: Set Analysis ignore not working?

arulsettu — Wed, 07 Oct 2015 12:11:40 GMT

post your code

Re: Set Analysis ignore not working?

gautik92 — Wed, 07 Oct 2015 12:13:10 GMT

post sample data

Re: Set Analysis ignore not working?

Wed, 07 Oct 2015 12:21:50 GMT

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)

Re: Set Analysis ignore not working?

stigchel — Wed, 07 Oct 2015 12:28:14 GMT

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)

Re: Set Analysis ignore not working?

sudeepkm — Wed, 07 Oct 2015 12:28:57 GMT

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

Re: Set Analysis ignore not working?

Wed, 07 Oct 2015 12:39:59 GMT

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

Re: Set Analysis ignore not working?

Wed, 07 Oct 2015 12:56:18 GMT

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?

Re: Set Analysis ignore not working?

stigchel — Wed, 07 Oct 2015 13:00:35 GMT

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

Re: Set Analysis ignore not working?

tresB — Wed, 07 Oct 2015 13:01:29 GMT

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.

Re: Set Analysis ignore not working?

Wed, 07 Oct 2015 13:10:30 GMT

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)

Re: Set Analysis ignore not working?

Wed, 07 Oct 2015 13:34:13 GMT

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

Re: Set Analysis ignore not working?

stigchel — Wed, 07 Oct 2015 13:50:25 GMT

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

Re: Set Analysis ignore not working?

Wed, 07 Oct 2015 13:56:17 GMT

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

Re: Set Analysis ignore not working?

stigchel — Wed, 07 Oct 2015 14:04:14 GMT

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