5 Replies Latest reply on Jul 11, 2016 12:41 PM by Jimmy Barber

    Apply a filter based off a snapshot in time?

    Jimmy Barber

      I have a data set that looks like below:

      Columns: Week, Item, Store, Status, Sales

       

      Week= what week of year it is

      Item= the description of the item sold

      Store= what store did the item sell in

      Status= was the item an active item or an inactive item

      Sales= what was the total transaction amount

       

      I am wanting to capture the item store combinations that are active in week 5, and then look at how those combinations sold throughout the rest of the weeks?

       

      Any ideas on this?

       

      I tried using Fixed function but that then filters for week 5, and I'm needing to look at all weeks, given the criteria active in week 5.

       

      Thanks for the help!

        • 1. Re: Apply a filter based off a snapshot in time?
          Jimmy Barber

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

          • 2. Re: Apply a filter based off a snapshot in time?
            Joe Oppelt

            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?

            • 3. Re: Apply a filter based off a snapshot in time?
              Jimmy Barber

              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!

              • 4. Re: Apply a filter based off a snapshot in time?
                Joe Oppelt

                See attached.

                 

                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.

                • 5. Re: Apply a filter based off a snapshot in time?
                  Jimmy Barber

                  Mind blown. Thanks so much Joe! Super helpful