topic Re: Set analysis without result although unique result possible via UI in QlikView

Set analysis without result although unique result possible via UI

andreas_koehler — Wed, 25 Nov 2020 16:16:04 GMT

Dear all,

This seems to be a general issue when set analysis gives a different result (or no result) compared to when a selection is done via a list box.

An example:

There are 3 conditions in a set point:

=avg(

{$<

Type -={Yellow},

Fruit={"=rank(aggr(max(Out_Stamp-In_Stamp),Fruit),1)<=$(vNumberLongestInShelf)"},

StorageNo={"=max(Out_Stamp-In_Stamp)"}

Out_Stamp-In_Stamp)

It turns out that the fruits being longest in shelf are all 'yellow'.

Now if I chose a smaller number than this the set analysis gives no result when I have no UI selection.

When I restrict via List box to Type = {Green}, I get a result.

When I do not use the set restriction of Type -={Yellow} then I get a result.

So the UI restrictions via List box limits the selection of all three parameters and the set analysis does not.

How do I get the average of Shelf-time of the fruits that are not yellow and with the longest duration in shelf per storage#?

Thanks for any help. Seems to me a very essential element to understand when dealing with Set analysis.

Andreas

Re: Set analysis without result although unique result possible via UI

sunny_talwar — Tue, 26 Jul 2016 01:04:15 GMT

This is not a straight forward question that you ask and would require some playing around with the data using different expressions. Would you be able to provide a sample with expected output?

Re: Set analysis without result although unique result possible via UI

andreas_koehler — Tue, 26 Jul 2016 01:29:33 GMT

Sunny,

need to make one. I did not provide one in the first instance as I thought that this is a generic problem.

Re: Set analysis without result although unique result possible via UI

marcus_sommer — Tue, 26 Jul 2016 05:31:29 GMT

I think the reason is the position where the conditions are applied - this meant within the inner or the outer aggregation:

=avg(

{$<

Fruit={"=rank(aggr(max({$< Type -={Yellow}>} Out_Stamp-In_Stamp),Fruit),1)<=$(vNumberLongestInShelf)"},

StorageNo={"=max(Out_Stamp-In_Stamp)"}

Out_Stamp-In_Stamp)

- Marcus

Re: Set analysis without result although unique result possible via UI

andreas_koehler — Wed, 27 Jul 2016 13:46:50 GMT

Thanks Markus,

always good to have a refresher from HICs excellent blogs.

I checked against these blogs but could not find the issue.

Guys,

I added an example. I also textboxed concats of StorageNo and Fruit.

It is about:

=avg(

{$<Color -= {Yellow}

,Fruit={"=rank(aggr(max(Out_Stamp-In_Stamp),Fruit),1)<=$(vRank)"}

,StorageNo={"=max(aggr(max(Out_Stamp-In_Stamp),StorageNo))"}

Out_Stamp-In_Stamp)

I changed the search expression for StorageNo.

Still there is no result and interestingly there are wrong results for StorageNo and Fruit.

The result of vRank = 1 should be 13.7 days, Apple, 2 (Fruit, StorageNo) and

for vRank = 2 it should be 8.7 days with Apple,2 and Cherries,1.

Thanks for any help.

Cheers,

Andreas

Re: Set analysis without result although unique result possible via UI

sunny_talwar — Wed, 27 Jul 2016 20:32:05 GMT

How about this:

=Avg({$<Color -= {Yellow}, Fruit = {"=Rank(Aggr(Max({<Color -= {Yellow}>} Out_Stamp-In_Stamp), Fruit), 1) <= $(vRank)"}, StorageNo = {"=Max({<Color -= {Yellow}>} Aggr(Max({<Color -= {Yellow}>} Out_Stamp-In_Stamp), StorageNo))"}>} Out_Stamp-In_Stamp)

Re: Set analysis without result although unique result possible via UI

andreas_koehler — Wed, 27 Jul 2016 20:47:59 GMT

Sunny,

thanks for this solution. My mistake that I was not really able to relate HICs comment in Pitfalls of the Aggr function

So, there are both inner and outer aggregation functions. Where do I put my Set Analysis expression?

The answer is “In both”. It is usually not enough to have it in only one of the levels.

to this problem. This was a real eye opener to me.

[EDIT]

While it is a solution for the example it does not resolve the real case. I hope you are okay me we leaving this as unresolved.

Re: Set analysis without result although unique result possible via UI

swuehl — Wed, 27 Jul 2016 21:02:42 GMT

And there is seldom a reason to use aggr() at all in the advanced search of a field modifier, especially when grouping by the set modifier field itself.

=Avg(

{$<Color -= {Yellow}, Fruit = {"=Rank(Max({<Color -= {Yellow}>} Out_Stamp-In_Stamp), 1) <= $(vRank)"}

,StorageNo = {"=Sum({<Color -= {Yellow}>} Out_Stamp-In_Stamp)"} >}

Out_Stamp-In_Stamp)

Should return the same (without really understanding what you are trying to accomplish, haven't read all of the posts).

Re: Set analysis without result although unique result possible via UI

andreas_koehler — Wed, 27 Jul 2016 22:29:15 GMT

I started like this but ran into a problem as I did not include the set modifier for Color into the one for Fruit and StorageNo. My false assumption was that I have to create the aggr() in the search expression.

Thanks Stefan for correcting this.

[Edit]

While your solution works nicely in the example, it does not give the correct result in the real application. The second set (StorageNo) seems to have no effect.If I limit it by a list box, I get the correct result.