Set Analysis/Filtering Issue

[#1]

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.

[sunny_talwar]

Try this expression:

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

[#3]

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(...)).

[sunny_talwar]

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.

[Anonymous]

mmm try this.

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

[#6]

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

[#7]

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.

[sunny_talwar]

Just period as dimension or Period and item as dimension?

[#9]

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)

[#10]

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