3 Replies Latest reply on Jun 18, 2018 4:37 PM by Deepak Rai

    Comp Store Filter

    Britney Walsh

      Hi everyone,

       

      I'm hoping someone can help!

       

      I am trying to create a filter that will allow me to view comp stores and non-comp stores sales. Here, a comp store is one that has sales for all of 2017 and YTD 2018 (as of today this would be 75 weeks of data). Ideally, the filter would be dynamic, based on a max date so there is as little manual updating as possible. The filter will need to be applied to a table like this one:

      I have attached a workbook showing weekly sales by store number and am using Tableau Version 10.5.

       

      Any help would be greatly appreciated!

       

      Thank you,

      Britney

        • 1. Re: Comp Store Filter
          Jim Dehner

          Hi

           

          see the attached

           

          thenis the formula I used

           

          this is not the same data that produced your image so it is a little difficult to check

          but it returns this

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Comp Store Filter
            Britney Walsh

            Hi Jim!

             

            I apologize, some of the data I meant to include didn't go through. I have attached an updated workbook and brought in your suggested formulas. It seems that the stores with partial year data are being included in the "Comp Store" filter?

             

            One idea I had was to use Countd to determine how many weeks of sales each store has and compare that to the maximum (based on 2017-2018 sales) to figure out which stores qualify as a Comp store. The main problem I was running into was I couldn't use the Max and Countd formulas at the same time since they're both aggregates.

             

            Do you have any other suggestions or maybe an edit to your previous formulas?

             

            Thank you so much for your help!

             

            Britney

            • 3. Re: Comp Store Filter
              Deepak Rai

              Hi Britney,

              Please Check The Screenshots and attached. This is  Some Out of the Box  Type Solution Please Have a look. So These are my Calcs

              This is just Showing your actual data and replaces Nulls with 0

              Next Step Converts your actual to 1 and keep 0 also

              I could have done it in a single step too, but wanted to Show Your actuals

               

              The Final Calc Goes across to Check Dynamically if your SUM across Table is >=75, Then it is Comp otherwise Non Comp, so You will get only Complete Rows.

               

              Thnaks

              Deepak

              If it Helps, Pl mark it Helpful and CORRECT to Close Thread