Been working through this a couple hours now, still no progress.
Worked through creating a parameter for the date I wanted, but that's then effecting all of my data. Let me know if you guys have any ideas..
Posting a workbook with some sample data would help here.
You can create a conditional filter.
For example, you could put something like this on [Item]
SUM(IF [Week] = 5 THEN 1 ELSE 0 END) > 0
this would get you all [ITEM]s that have at least one [Week]=5 record. It would get all records for that item.
You are looking to interplay [Item] and [Store], so I would have to mess with this to see how to get it to cooperate for both fields -- and I would guess the output would depend on whether you want all [Item]s with 5 AND all [Store]s with 5, or only [Item]s and [Store]s that both have 5.
(And you could parameterize this so that it's looking for the value contained in a parameter, not just a hard-coded 5.)
Can you provide a sample workbook?
Apologize for not attaching sample!
I currently have this workbook filtered for Year/Week 201504--great idea with the parameter option.
My goal: Take the unique item/store combination's that are Active (1) in Year/Week 201504, and then look at sales for that combination in other weeks.
Let me know what you think, thanks Joe!
Comp Test.twbx 27.9 KB
Sheet2 is a dup of Sheet 1. I just added a filter to get Active=1. (BTW, having this field made this easier. Glad it's there.)
Sheet3 is dup of Sheet2, but I made a calc to concatenate Store and Item. Then I put on the conditional filter here that I described above. Right click this field in the Filters shelf and edit filter. Go to CONDITION tab. See what I did there. You'll see that the results of Sheet2 and Sheet 3 are identical.
Sheer4 is a dup of Sheet3, I just took off the Year/Week filter. Now you get all the Store-Item combos that you had for "201504", but you get them for all values of [Year/Week] So you'll have only Active=1 in "201504", but any possible value in the other periods.
And, of course, you can do the parameter thing. Just replace the hard-code value in the conditional filter with the parameter.
Comp Test A.twbx 43.1 KB
Mind blown. Thanks so much Joe! Super helpful