4 Replies Latest reply on May 10, 2017 10:18 AM by Ritesh Bisht

    Opening Weekend Calculation

    Vandana Samtani

      Hi guys, I have a requirement to calculate opening weekend amount for products launched during the week. For example  - if the product 1 is launched on 03/04/2017 (Thursday) then calculate the total amount from Thursday to first Sunday of that week.. i.e. 03/04 to 03/07.

       

      Product 2 launched on 03/05/2017(Friday) then calculate the total amount from Friday to First Sunday.  i.3. 03/05 to 03/07

       

      Product 3 launched on 03/01/2017 (Monday) then calculate the total amount form Monday to First Sunday i.e. 03/01 to 03/07

       

      The business wants to know what is the sales of week1 from product launch date until First Sunday.

       

      I have attached the sample work book and test data. Any suggestions would be helpful. Thanks, Vandana

        • 1. Re: Opening Weekend Calculation
          Ritesh Bisht

          STEP 1

          Calculation for first Sunday

           

          First Sunday

          =

          if [Launch Day of the week]='Mon' then [Launch Date]+6 ELSEIF

          [Launch Day of the week]='Tue' then [Launch Date]+5 ELSEIF

          [Launch Day of the week]='Wed' then [Launch Date]+4 ELSEIF

          [Launch Day of the week]='Thu' then [Launch Date]+3 ELSEIF

          [Launch Day of the week]='Fri' then [Launch Date]+2 ELSEIF

          [Launch Day of the week]='Sat' then [Launch Date]+1 ELSEIF

          [Launch Day of the week]='Sun' then [Launch Date]+7 END

           

          Drag this to rows

           

          STEP 2

          Another Calculation

           

          "Till Sunday"=

          if [Shopping Date] <= [First Sunday] then 'true' else 'false' end

           

          Drag this to filter Select "True"

           

          STEP 3

           

          Calculation till Sunday

           

          Amount Sum Till Sunday

          { EXCLUDE [First Sunday],[Shopping Date] : SUM([Amount])}

           

          Drag this to rows.

           

          You can download it here as below

          https://public.tableau.com/static/images/Te/Test_2171/Sheet2/1.png

           

           

          Thanks,

          Ritesh

          1 of 1 people found this helpful
          • 2. Re: Opening Weekend Calculation
            Zhouyi Zhang

            Hi, Vandana

             

            You can create a calculation field as shown below and put it to filter.

             

            here is the result when filtering the open weekend only.

             

             

            Let me know if it works for you. I attached the workbook as well for your reference.

             

            ZZ

            1 of 1 people found this helpful
            • 3. Re: Opening Weekend Calculation
              Vandana Samtani

              Hi Ritesh, Thanks for your suggestion! Quick Question - Is there a way I can create a measure so that I can add the opening weekend as well as the overall totals in one visualization like a bar chart. Thanks for your help again! Vandana

              • 4. Re: Opening Weekend Calculation
                Ritesh Bisht

                Hi Vandana,

                 

                Sure, also please mark it as completed/helpful if it satisfies you !

                 

                STEP 1

                 

                Screen Shot 2017-05-10 at 10.34.17 PM.png

                 

                STEP 2

                 

                Screen Shot 2017-05-10 at 10.33.57 PM.png

                 

                 

                STEP 3

                 

                Screen Shot 2017-05-10 at 10.33.15 PM.png

                 

                STEP 4

                 

                Drag Launch date and      Till Sunday to Columns

                 

                Include Sum to rows

                STEP 5

                 

                 

                 

                 

                Go to Analysis --> Total --->Add all sub-total

                 

                Screen Shot 2017-05-10 at 10.35.16 PM.png

                 

                 

                 

                STEP 5

                 

                Drag product name to colour (only if needed )

                Please see the workbook here ---->       Tableau Public  (You can download that as well )

                 

                 

                Screen Shot 2017-05-10 at 10.48.18 PM.png

                 

                Thanks,

                Ritesh

                1 of 1 people found this helpful