topic Re: Set Analysis/Filtering Issue in QlikView

Set Analysis/Filtering Issue

Wed, 02 Dec 2015 15:25:03 GMT

Please see attached. I'm having an issue whereby my set analysis on a column value in a straight table is causing my filter to work in an undesirable manner. I've distilled the issue into the attached example and application.

I have a straght table with item and period (year/month) as dimensions. I have a stock value and, for each period, the number of months since the last sale. My two table expressions are:
1. =only(months_since_last_sale)
2. =avg({<period=,months_since_last_sale=>} stock_value)

But because of months_since_last_sale= in the set analysis on the second expression, all items remain when a months_since_last_sale value is selected. Ideally, the other items should disappear when a value is selected.

Here is my data:

item, period, stock_value, months_since_last_sale
Shoes, 201510, 10, 0
Shoes, 201511, 10, 0
Shoes, 201512, 20, 1
Socks, 201510, 5, 1
Socks, 201511, 5, 2
Socks, 201512, 5, 0
Slippers, 201510, 10, 5
Slippers, 201511, 20, 6
Slippers, 201512, 30, 7
Sandals, 201510, 10, 10
Sandals, 201511, 15, 0
Sandals, 201512, 10, 1

The problem is restated in the attached application. Thanks for taking a look.

Re: Set Analysis/Filtering Issue

sunny_talwar — Wed, 02 Dec 2015 15:37:35 GMT

Try this expression:

=If(Len(Trim(Only(months_since_last_sale))) > 0, Avg({<period=,months_since_last_sale=>} stock_value)) //Avg Mo Value

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 18:59:16 GMT

Thanks. I considered a solution like this but was hoping for something more elegant. My production application has ten or more columns that will require the update. Also, it's crunching a lot of data, and I'm concerned about performance. For lack of anything better, I'll give it a try.

As a side note to your suggestion, I wonder if IsNull(...) will perform more optimally than Len(Trim(...)).

Re: Set Analysis/Filtering Issue

sunny_talwar — Wed, 02 Dec 2015 19:29:47 GMT

Not sure how the performance differ, but I usually prefer using Len(Trim()) because it is optimal in a sense that it takes out white spaces out of equation together with actual nulls.

Re: Set Analysis/Filtering Issue

Anonymous — Wed, 02 Dec 2015 19:49:44 GMT

mmm try this.

=avg({<period=,months_since_last_sale=>} stock_value)*(Column(1)/Column(1)) //Avg Mo Value

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 20:03:30 GMT

Thanks. It doesn't work if the column(1) value is zero.

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 20:04:54 GMT

Overall performance doesn't seem too bad. The problem I'm running into now is that your solution doesn't work for mini chart renderings. Change the Avg() to Sum() and the Representation to Mini Chart with a dimension on Period.

Re: Set Analysis/Filtering Issue

sunny_talwar — Wed, 02 Dec 2015 20:07:27 GMT

Just period as dimension or Period and item as dimension?

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 20:09:01 GMT

Add the new field like months_since_last_sale with different flag name and use flag filed in you set analysis.

months_since_last_sale as _flag_months_last_sale

=avg({<period=, _flag_months_last_sale=>} stock_value)

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 20:13:28 GMT

Period. You can only specify one dimension on a Mini Chart. See attached. !

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 21:25:32 GMT

Either I'm not understanding or it doesn't work. Can you update the app with a working example?

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 21:43:09 GMT

I looked into your data and why you have to ignore the Period & months_since_last_sale first place to calculate the avg stock value.

Re: Set Analysis/Filtering Issue

Wed, 02 Dec 2015 21:46:23 GMT

Because a single period must be selected -- and the average has to be taken over all periods and (correspondingly) over all months_since_last_sale values.