7 Replies Latest reply on Aug 1, 2016 12:38 PM by Joe Oppelt

    Like4Like Sales

    David Heinz

      Hello all,

       

      I recently tried to create a calculation that shows me the like4like sales for the hitherto existing weeks this year.

       

      What I want tableau to do is to only take into account the stores that were open in week 1 2015 as well as in week 1 in 2016.

       

      CASE [Like4Like Auswahl]

      WHEN 1 THEN

                 if DATE([Datum Eroeffnung]) <= [DatEroeff] then 1

                 ELSEIF isnull([Datum Schliessung]) THEN 1

                 ELSEIF DATE([Datum Schliessung]) <= [DatSchliess] then 1

                 else 0

                 END

      WHEN 2 THEN 2

      END

       

      [Datum Eroeffnung] is the dimension that sets the opening date of the store, [DatEroeff] is a parameter I used to set the date at December 31st 2015

      [Datum Schliessung] is the dimension that sets the closing date of a store, [DatSchliess] is a paramter I used to set the date at Jan 1st 2016

       

      I then created another paramter [Like4Like Auswahl] that shows "like4like" if the calculated field is "1" and "all" if it's "2".

       

      The problem is, that the calculation doesn't compare the opening and closing dates of the store on a weekly base but on a yearly base.

       

      Do you have any idea how I would have to adapt the calculation to only compare like4like sales on a weekly base? (somehow like shown in the picture?)

        • 1. Re: Like4Like Sales
          Joe Oppelt

          David -- Trying to juggle all the facts of your description without a hands-on example to work with makes my head spin.


          Can you provide a sample workbook?  I'd like to take a look.

          • 2. Re: Like4Like Sales
            David Heinz

            Hi Joe,

             

            attached you find a sample workbook. I hope it provides most of the information you need,

            Again, what I want to show is a weekly based sales comparison that considers only the stores, that where open during this specific week.

             

            All the best,

             

            David

            • 3. Re: Like4Like Sales
              Joe Oppelt

              Let me understand a little more about what you have here.


              Is it sufficient to say that if a store's open date is >= week-1 of 2015, and if that store's closing date is <= week-1 of 2016, then (and only then) we should include it in the sheet?  (And I see nulls in Closing Date in the data.  We can take that into account as well.  If null than we assume it's not closed.)

               

              Basically what I think you're looking to do is to filter into the sheet only stores that were open during a specific range of time.  (And if I'm not mistaken, they have to be open the complete range of that time.)

               

              Is there ever a chance that a store could close and then reopen within the set of data?  (Or for any given store, it will have only one open date and only one closed date throughout the data.)

              • 4. Re: Like4Like Sales
                David Heinz

                The calculation I used is only valid for the first week in 2016.

                If the store's open date is <= 01.01.2016, than tableau should include the store because it was open in the first week 2015 as well as in the first week 2016.

                If the store's closing date is null, it should also be included (under the condition that the opening date is <= 01.01.2016)

                If the the store's closing date is <= 01.01.2015, it should also be included. If the closing date would be > 04.01.2015, we would take into account sales data from a store that was open in the first week 2015 but is closed in the first week 2016.

                 

                What I want to compare is an equal number of stores for every week of the year. (The stores that were open in the first week in 2015 as well as in the first week in 2016, the stores that were open in the second week in 2015 as well as in the second week in 2016 and so on)

                 

                There is no chance that a store could close and reopen within the data set.

                • 5. Re: Like4Like Sales
                  Joe Oppelt

                  I still don't understand what you are aiming for.

                   

                  See the attached.  Point out some specific examples of what you would want included and excluded.  Are you looking to evaluate this on a week-by-week basis?

                   

                  For instance, see "10206 Paris".  It closed in week 9.   Would you want Paris included, but only weeks 1 through 9?  And Munich would not be included at all?

                   

                  (Note:  I modified [DateCLo] to replace "null" with TODAY() so that we have an actual date to look at for this exercise.)

                  • 6. Re: Like4Like Sales
                    David Heinz

                    Exactly, in your example, Paris should only be included for the weeks 1 through 9.

                    Munich, Milan, Prague, Barcelona and Madrid shouldn't be inlcuded at all.

                     

                    Köln should only be considered for the weeks 27 to 52, because it opened in week 27 in 2015.

                     

                    Yes, I want to evaluate this on a week-by-week basis. Every week should only consider the stores that were open in this specific week in both years, 2015 and 2016.

                     

                    Thanks a lot!

                    • 7. Re: Like4Like Sales
                      Joe Oppelt

                      See attached.

                       

                      I added two new calcs to Sheet 2.  One is to evaluate whether we should keep that data based on the opening, and one is based on the closing.

                       

                      In sheet 3 I added a filter calc.  If both evaluations are "Keep" then grab it.

                       

                      In the end, you don't need to display all that intermediate stuff.  I just left it on there to show what's going on.